In this article, I’m going to walk through the steps necessary to migrate a SQL Server 2008 DB to a server running SQL Server 2005 using only free tools or tools that come with SQL Server. The short description of the process is: Generate a script on SQL Server 2008, then run it on SQL Server 2005. However, there are a few potential pitfalls, and I’m going to show you what they are and how to circumvent them.
Generate Scripts on SQL Server 2008
Connect to the SQL Server 2008 server with Object Explorer. Locate the database and right-click on it to open a context menu. Make sure you choose the right context menu item to get started. Do not choose Script Database as | CREATE To.
Instead, what you want is Tasks | Generate Scripts…
This will open the Generate and Publish Scripts wizard. The first choice comes in the Choose Objects step. Unless you intend to restore anything less than the entire DB on the SQL Server 2005 server, keep the default choice to script the entire database.
In the next step, there are a few more choices to make. I recommend keeping the defaults visible below.
You want to save to a file because any database of meaningful size and complexity will generate a very large script; much too large to go into your clipboard and even too large to open in SSMS. I recommend keeping it all in a single file, because the creation statements will be scripted in the correct order for you. As long as you have a text editor capable of modifying the script (of which I will recommend a few), this is preferable to creating the objects in order manually.
Setting the Advanced Options
Here are where the pitfalls lay. The defaults here are nothing like what you would want to choose to script an entire database. Below are the default settings and my recommended settings to obtain a script that will faithfully recreate the source database without leaving anything important out. I highlighted the settings that are different from the defaults.
| Property | Default | Recommended | Comments |
| ANSI Padding | True | True | This is the future. Don’t change it unless you have good reason to. |
| Append to File | False | False | Keep the script separated from others for simplicity. |
| Continue scripting on Error | False | False | Stop ASAP to fix the error, then retry. |
| Convert UDDTs to Base Types | False | False | The UDDTs will be included in the script, so might as well use them if they’re already in use. |
| Generate Script for Dependent Objects | True | True | Everything is getting scripted anyway |
| Include Descriptive Headers | True | True | The headers aren’t very descriptive, but they aren’t very big either. Not much impact to either choice. |
| Include IF NOT EXISTS | False | False | For restoring an entire DB, there should not be any objects in the target anyway |
| Include system constraint names | False | True | This is the safe choice in case there is any code dependent on the system-generated name. Best practice is to name objects yourself. |
| Include unsupported statements | False | False | No reason to include them unless you don’t want the script to be runnable! |
| Schema qualify object names | True | True | This is just good practice. You should do this in your own SQL if you don’t already. |
| Script Bindings | False | True | This is a little obscure; it means to bind old-style rules and defaults to columns. If the source database has these objects, the scripted DB needs to bind them or they won’t work. |
| Script Collation | False | True | If collation has been changed in the source, it is probably for good reason, and you want the target to retain the setting. |
| Script Defaults | True | True | |
| Script DROP and CREATE | Script CREATE | Script CREATE | For restoring an entire DB to a new DB, this is all you need. |
| Script Extended Properties | True | True | |
| Script for Server Version | SQL Server 2008 R2 (or installed version) | SQL Server 2005 (or other desired target version) | |
| Script for the database engine type | Stand-alone instance | Stand-alone instance | The only other option is SQL Azure. If that’s your target, choose that. |
| Script Logins | False | False | Only script them if none of the logins exist on the target server; otherwise, it will probably be easier to add them after the fact. |
| Script Object-Level Permissions | False | True | If objects have permissions applied to them in the source, you want to keep them in the target. |
| Script Statistics | Do not script statistics | Do not script statistics | The default is fine. Statistics can always be recreated later. |
| Script USE DATABASE | True | True | This ensures that the objects are created in the correct database |
| Types of data to script | Schema only | Schema and data | Another option would be to script schema only to one script, then go through the wizard again to script data only. |
| Script Change Tracking | False | True | Leave it false if you do have change tracking enabled in the source but don’t want it enabled in the target |
| Script Check Constraints | True | True | |
| Script Data Compression Options | False | False | You can always enable compression later |
| Script Foreign Keys | True | True | |
| Script Full-Text Indexes | False | True | If your source has full text indexes, you will probably want them in the target |
| Script Indexes | True | True | |
| Script Primary Keys | True | True | |
| Script Triggers | False | True | I am mystified why the default is false for this one. Omitting triggers can cause a major loss of functionality. |
| Script Unique Keys | True | True | |
Once you have set the advanced options, click Next, review your settings, and click Next again to generate the script.
Run the Script on SQL Server 2005
Now you should have a pretty large .sql file on your 2008 server. This very large file can present its own challenges, because most editors won’t be able to load it, making it difficult to edit, run, and debug if a problem occurs. The .sql file I had to deal with was about 640 MB – too large for Notepad, Wordpad, Notepad++, Notepad2, Metapad, TED Notepad, or SSMS.
Free Text Editors Capable of Editing Large Files
Most text editors that support editing large files are commercial. However, there are a few free editors that can handle such files. The friendliest of the pack is AkelPad. This notepad replacement will open files using available memory and boasts several plugins that make it a capable code editor with minimal user effort. Other notable free text editors that support large files are Emacs and Vim. These editors are very powerful, but at the cost of a steep learning curve. If you’ve never used them before and don’t want to take the time to learn how these editors work, I will point you back to AkelPad.
The main reason you need a text editor that can open your script is that the generated script will start with a CREATE DATABASE statement that contains paths to the data and log files as they exist on the source server. Unless the target server has exactly the same folder structure, the CREATE DATABASE statement will fail. You will need to open the file and either edit the paths to match the folder structure on the 2005 server, or remove the filespec portion of the statement entirely, which means also not being able to specify size, maxsize, and filegrowth. Either way, a little editing will be necessary.
How to Run the Large Script
I already mentioned that SSMS can’t handle opening such large files, so now how can you run it? The answer is to use sqlcmd.exe, the command-line utility that lets you run queries and scripts. Assuming it’s already in your path, here’s how you would run the script from a command prompt in the same directory:
sqlcmd –S server –U login –P password –b –x –i script.sql
Most of the flags should be self-explanatory by the value you pass to them, but there are two that require explanation. The –b flag specifies that the script should stop executing on error. Just like with script generation, if an error occurs, you should fix the error and then re-run the entire thing from the beginning. The –x flag disables sqlcmd parameter substitution. This feature is great for scripting repetitive tasks, but it can cause problems if you are loading data that looks like sqlcmd parameters, which are formatted as $(variable). So, for example, if your DB contained JQuery selector string data, sqlcmd would think that those selectors were variables and throw an error, because no matching variable could be found to substitute.
Once you execute the above command, the script will begin to execute. The generated script has print statements in it to output its progress, and sqlcmd echoes these statements to the console output. The script will take a while to run, but once it’s finished, you will have a fully restored database that started on a 2008 server, restored to a 2005 server.