There are situations where you might need to migrate newer-version SQL Server databases to older versions of the MSSQL engine, such as in various QA or testing scenarios, or after an upgrade that for any number of reasons calls for a rollback but needs to keep existing data. Unfortunately, backup/restore does not work when going from a newer to an older version of Microsoft SQL Server– it is not possible to restore a SQL 2012 database backup into a SQL 2005 server, for example. So in order to accomplish this, we have to take a different approach.

For learning purposes only, here for your enjoyment is one end-to-end example of how you can move a database from SQL Server 2012 to SQL Server 2005. You will see along the way that this operation can be a bit more complicated than it might seem at first. So, who is ready to get their hands dirty?

NOTE: There are innumerable situations in which the techniques I elucidate here will not work. So, understand the following information is provided as-is with no warranty of any kind. ALWAYS make sure you have good backups. For many situations, the smartest move is to get expert help right away, before you make an already big problem into an unrecoverable disaster.

I prefer to do the structures (a.k.a. data definition language or DDL) and the data (a.k.a. data modification language or DML or just good ol’ “data”) separately and in different ways. This is for practical reasons. A lot of issues can come up, and this makes dealing with those a lot easier. I advise keeping the newer-version (source) database available for as long as possible also, so that re-scripting things can be done pretty easily. During troubleshooting of issues this can be a huge time-saver. As a last failsafe, I also keep a backup of the newer-version database squirreled-away somewhere safe and sound. JIC.

For part 1, I move DDL by generating a script in SSMS (SQL Server Management Studio). In my opinion doing this separately speeds up the process when it comes to troubleshooting problems with DDL directly in SSMS. Although this has gotten better and smarter with each successive Enterprise Manager and SSMS version, issues can still crop up which require human intervention. Particularly in an environment that has half-baked code lying around. Starting this way will increase your chances of success.

Then for part 2, moving data, I use the Import/Export Wizard in SSMS to produce an SSIS package (.dtsx) that I run. This approach assumes the source server’s SSIS can talk to the destination MSSQL instance. I just think it’s the best tool for the job– but opinions on that do vary, and at the end I included some summary notes on some alternative approaches.

PART 1 – DDL

For the first part, we are going to generate a script for the source database’s DDL (empty tables, views, stored procedures, etc.). We will use this script to create an empty destination database that has everything but the data. Let’s start by making a backup of the source database. You never know if you’ll need it, and storage is cheap. (Also, do your future self a favor and name it something that’ll clue you in to what it was for, when you come across it 2 years from now.) Then…

Right-click the source database, go to Tasks > Generate scripts

Advanced scripting options >
Script for SQL Server Version > 2005
Script object-level permissions > True
Script owner > True
Types of data to script > Schema only
Script triggers > True

NOTE: If you do need to move Logins, or permissions, this can get dicey. For moving logins you’ll generally want to script out SIDs and hashes of the passwords with something like https://support.microsoft.com/en-us/kb/918992. If you move object-level permissions here, you also might run into some scenarios that you can’t cure using sp_change_users_login with auto_fix alone. Maybe I’ll cover those in a future blog post. For now, just be aware.

After it is done, on the “Save or Publish Scripts” screen, make sure you scroll up and down and see that everything has a Result of “Success.”

Try running the DDL script on your destination instance in SSMS. If the planets are aligned, and your guardian angel isn’t asleep at the wheel, it will succeed, and this step is done.

Otherwise, work through all of your errors. You can double-click an error in the output window, and it will take you to the problem area in the script. Note that one problem in a create statement can result in a bunch of subsequent errors, so start with the first error mentioning a particular object.

Note that you might see

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'pr_YourProcNameHere'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

Don’t worry about this one. (Not that you would anyhow, based on the message itself.)

Also, you might find some problems here that you can disregard, perhaps some midprocess development that doesn’t work yet. In a dev environment, there can be a lot of proverbial “dirty dishes in the sink”. Get that stuff sorted out before moving forward, to minimize your risks of mucking things up.

PART 2 – Data

Data. You will use the Import/Export Wizard to export all of the data from the source to the destination. Before you begin, you need to make sure your source server has access to your destination instance. Also, we will need to do some additional preparation. At the destination there could be things there like IDENTITY columns, constraints, foreign keys, and triggers, that would take unwanted actions on the data we’re looking to move, so we will have to take care of those.

Step one for destination prep is to disable triggers and set constraints to not check values:

use YourDestinationEmptyDatabase
exec sp_MSforeachtable @command1='ALTER TABLE ? DISABLE TRIGGER ALL'
exec sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'

(Why don’t we use sp_MSforeachtable to turn on IDENTITY_INSERT? Because it can only be done for a single table at a time. This is why it has to be in the Export itself, which we’ll get to.)

Step two for destination prep is to script out FKs, save your script, and delete FKs in the empty database. I do this because in the Export, I want to “Delete rows in destination table” so the package can be repeatedly run, like if some problem comes up that isn’t specific to the internals of the package. (Of course, that can also make it much more dangerous if it gets run in the wrong place! You have been warned.) Selecting this for the export truncates the tables right before importing, which doesn’t play nicely with foreign keys at all. To do this, run this script that I picked up courtesy of http://www.connectsql.com/2011/05/sql-server-createdrop-scripts-for-all.html (but don’t let me get in the way of your making your own…!)

SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(F.schema_id)) + '.'
 + QUOTENAME(OBJECT_NAME(F.parent_object_id)) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(F.name)
 + ' FOREIGN KEY ' + '(' + STUFF((
 SELECT ',' + QUOTENAME(COL_NAME(FC.parent_object_id, FC.parent_column_id)) 
 FROM SYS.FOREIGN_KEY_COLUMNS AS FC 
 WHERE F.OBJECT_ID = FC.constraint_object_id
 ORDER BY FC.constraint_column_id
 FOR XML PATH('')
 ),1,1,'') + ')'
 + ' REFERENCES ' + QUOTENAME(SCHEMA_NAME(RefObj.schema_id)) + '.'
 + QUOTENAME(OBJECT_NAME(F.referenced_object_id)) + ' ('
 + STUFF((
 SELECT ',' + QUOTENAME(COL_NAME(FC.referenced_object_id, FC.referenced_column_id)) 
 FROM SYS.FOREIGN_KEY_COLUMNS AS FC 
 WHERE F.OBJECT_ID = FC.constraint_object_id
 ORDER BY FC.constraint_column_id
 FOR XML PATH('')
 ),1,1,'') + ') '
 + CASE WHEN update_referential_action_desc = 'CASCADE' THEN ' ON UPDATE CASCADE'
 WHEN update_referential_action_desc = 'SET_DEFAULT' THEN ' ON UPDATE SET DEFAULT'
 WHEN update_referential_action_desc = 'SET_NULL' THEN ' ON UPDATE SET NULL' 
 ELSE '' END
 + CASE WHEN delete_referential_action_desc = 'CASCADE' THEN ' ON DELETE CASCADE'
 WHEN delete_referential_action_desc = 'SET_DEFAULT' THEN ' ON DELETE SET DEFAULT'
 WHEN delete_referential_action_desc = 'SET_NULL' THEN ' ON DELETE SET NULL'
 ELSE '' END
FROM SYS.FOREIGN_KEYS AS F
 INNER JOIN sys.objects RefObj ON RefObj.object_id = f.referenced_object_id
--where OBJECT_NAME(f.parent_object_id) = 'yourObjectName'

Now save this output somewhere safe for later, you will need it. Double-check that it is somewhere safe. Next, run

SELECT  'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'
        + OBJECT_NAME(F.parent_object_id) + ' DROP CONSTRAINT ' + F.name
FROM    SYS.FOREIGN_KEYS AS F
        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
--WHERE   OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

Save the script it outputs. Run it on the empty destination database to drop the foreign keys there.

Now we are ready to do the export/import.

Right-click database, Tasks > Export
Choose a data source should fill itself out automatically. Click Next.
Fill in your destination. Make sure you get this right! Click Next.
Copy data from one or more tables or views.
Select all tables by clicking the top-left checkbox.
Scroll down. Toward the bottom, de-select any selected Views. They have different icons. (This might be handy if you were going to do some changing of your data using views, but for our project, we already made those views on the destination.)

Start with first table, Edit Mappings, radio button “Delete rows in destination table”, check Identity Insert, Hit OK. Iterate through each table doing this. (shortcut keys: Alt+E, Alt+W, Alt+I, click OK, down arrow)
Click Next.
Uncheck “Run immediately”.
Check “Save SSIS package”, radio “File system” and assuming you have the luxury of doing so, select “Encrypt sensitive data with password”. Click Next.
Specify Name and path. Click Next.

On the destination instance, make sure you disconnect or close all query windows on the destination database.

On the source server, run cmd.exe as Administrator.

cd "C:\Program Files\Microsoft SQL Server\110\DTS\Binn"
dtexec /F C:\dba\your_file_here.dtsx

Depending on how much data you’re moving, this could take a little while! If fortune smiles upon you, it will succeed without any hangups. If not, commence troubleshooting now. (I do plan to cover one troubleshooting experience I had at this point in another post, but don’t want to get off-track here.)

Once you are done, compare rowcounts for some tables, make sure your identity columns are the right values for the right records, and anything else you need check in order to be sure the data was moved correctly. Remember, any work you do to be completely sure now– really “sure-sure”– will be at least an order of magnitude less than the work it would take to clean up a mess later (IF it even can be done at all! For further information, check out Wikipedia http://en.wikipedia.org/wiki/Entropy_(arrow_of_time), or try making scrambled eggs, and then attempting to reassemble them into what you started with).

Then, once you are sure-sure, you can run

exec sp_MSforeachtable @command1='ALTER TABLE ? ENABLE TRIGGER ALL'
exec sp_MSforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT ALL'

And to top it off, you can re-create the foreign keys using the script you set aside earlier.

Doublecheck permissions to make sure you had your ducks in a row through the process.

With that, you should be all set. Let the incoming queries commence!

NOTES ON OTHER APPROACHES:

GENERATE SCRIPT FOR DATA
Tasks > Generate scripts
Advanced scripting options
Script for SQL Server Version > 2005
Types of data to script > Data only

If your data script file is 2GB, or 200GB, good luck opening it in SSMS to troubleshoot problems– but of course there are other ways to tackle that.

Once you copy your generated data script over to the destination, you will want to run it using SQLCMD, as it could well be too large for SSMS (SQL Server Management Studio).

Open up a Command Prompt (Start > Run > Cmd, for example). For SQL 2005 destination server, you’ll do something like this:

cd "C:\Program Files\Microsoft SQL Server\90\Tools\binn"

sqlcmd -I -S myServer\instanceName -a 32767 -i C:\scripts\myScript.sql -o C:\scripts\data_import_output.txt

(You can find more details for sqlcmd syntax at https://msdn.microsoft.com/en-us/library/ms162773.aspx)

Also, I found when mucking my way through issues it was helpful to “set nocount on” so my output file didn’t fill up with a bunch of “(1 rows affected)” outputs. Since -q and -i are mutually-exclusive in SQLCMD, I had to make use of multiple input files for the -i switch. I made a setnocounton.sql file in the scripts folder that just contained

set nocount on

With that in place, I ran

sqlcmd -I -S myServer\instanceName -a 32767 -i C:\scripts\setnocounton.sql,C:\scripts\myScript.sql -o C:\scripts\data_import_output.tx

I’ve run into issues with various long text columns here, such as:

Msg 105, Level 15, State 1, Unclosed quotation mark after the character string

followed by

Msg 102, Level 15, State 1, Server Incorrect syntax near

My solution was to go with a different approach.

INSERT INTO LINKED SERVER

 insert into [YourDestinationEmptyDatabase].[dbo].[DocInfo]
 	select * from [servername\instancename,1433].YourDestinationEmptyDatabase.dbo.YourTableName

(repeat)

You’ll need to set identity_insert on each table on the destination, which might make it an easier option to pull the data from the destination side, instead of pushing it from the source side.

REPLICATION

Some folks suggest Replication (such as snapshots) for this work. In my mind, setting up Replication infrastructure just for this task can add a lot of overhead while increasing risk surface area; nonetheless, it is definitely another way to get the job done, and if you have everything there already, why not use it. Of course, there are plenty of little gotchas that can crop up there also, so have your reference materials handy.

Leave a Reply

Your email address will not be published. Required fields are marked *