SQL Server: Copy database from recent to older versions

Recently, I needed to copy a database from SQL Server 2012 to SQL Server 2008 R2.  Unfortunately, database backups from one version of SQL Server cannot be read by a lower version.  To work around this, I had to use the “Generate Scripts” function to first create the structure and then populate the database.

Whilst none of this is particularly complex, I thought it might be useful to collate the steps in one document…

Step 1: Generate structure scripts

  1. In the SQL Server instance that contains the database to copy, right-click on the Database in the Object Explorer, select Tasks, then select Generate Scripts.
  2. In the Wizard, progress to Choose Objects and make sure that “Script entire databases and all database objects” is selected.

    Screenshot: Select "entire database" option
    Select the “entire database” option.
  3. Progress to the “Set Scripting Options” stage.  Click on “Advanced” and make sure that the “Type of data to script” option is set to “Schema only”.  Also set the “Script for Server Version” option to the other SQL Server’s version (I still ran into incompatibility problems with the generated script, however).

    Screenshot: Advanced options in Generate Scripts
    Advanced options in Generate Scripts
  4. Finish off the Wizard, copy the generated scripts to a location that the other SQL Server instance can access and run them in that Management Studio.  Here are the main problems and fixes I ran into during this part of the process:
    1. Error message that “CONTAINMENT is marked as incorrect syntax”:  Just comment out the “CONTAINMENT = NONE” line (source: http://stackoverflow.com/a/15410854)
    2. Error message that CREATE Database commands can’t be run in a multi-transaction statement:  Just run the create statement separately.
    3. Error when trying to alter user to add role:  This command is not supported in older versions; change those commands to use “sp_addrolemember” (e.g. EXEC sp_addrolemember ‘db_datareader’, ‘Administrator’) (source: http://technet.microsoft.com/en-us/library/ms187750.aspx).
  5. If that all goes well, you should now have the database skeleton ready.  The next step is to import the data.
  6. Back on the originating SQL Server, go to Generate Scripts again, but this time in the Advanced options, set “Type of data to script” to “Data only”.  Because this will probably require a lot of space, in the “Set Scripting Options” screen, set “Files to generate” to “Single file per object”, to split up all of the scripts by table.  Finish the Wizard.
  7. You should now have a bunch of script files, ready to be run.  Copy the containing directory to a location the older SQL Server can access.  Because I had 219 generated files, to save on running them manually, I used the following batch script (create and run it from the containing folder):
    for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
    pause

    (source: http://stackoverflow.com/a/6504317)

  8. The above script may take a while to run, but after that, you should be done!

 

How do I summarize, in days, when each Opportunity was last modified?

More specifically, this information is usually intended to be outputted as an additional column, in the Opportunity pipeline screen.

How the "Days in Stage" column appears in the Opportunity pipeline.
How the “Days in Stage” column appears in the Opportunity pipeline.

To do this, you’ll need to:

  1. Modify the database view used by the Opportunity pipeline.
  2. Create a translation.
  3. Add the new column to the pipeline screen.

 

Step 1: Modify vListOpportunities

The Pipeline screen uses the vListOpportunities view, to provide the columns for it to display.  In Sage CRM, go to the Administration section, select the Opportunties entity and under the Views tab, update the view definition to include our new field:

CAST((DATEDIFF(hh, wi.WkIn_UpdatedDate, GETDATE()) / CAST(24 AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS 'oppo_daysinstage',

and an inner join to the Workflow Instance table (we’re using the Updated Date on the workflow instance as an indicator of when the Opportunity was last modified; you could just use the Opportunity’s Updated Date, but then that wouldn’t include workflow progression).

INNER JOIN WorkflowInstance AS wi 
		ON wi.Wkin_CurrentEntityId = 10 
			AND wi.WkIn_CurrentRecordId = oppo_opportunityid

Your view definition should end up looking like the following:

View definition for vListOpportunities.
View definition for vListOpportunities.

Step 2: Add a translation for the new field

To display a user-friendly name for the oppo_daysinstage field, go to Administration -> Customisation -> Translations and create a new translation using the following details:

Parameters for a new oppo_daysinstage translation.
Parameters for a new oppo_daysinstage translation.

 

Step 3: Add “Days in Stage” to the Pipeline screen

For the last step, go to Administration -> Customisation -> Opportunities -> Lists and select the Opportunities List item.  Select the “Days in Stage” field in the Field drop-down, click Add and then save your changes.

Adding the "Days in Stage" field as a new column.
Adding the “Days in Stage” field as a new column.

When you go back to the Dashboard, your Opportunities Pipeline should now display this brand new field!