It is widely known that you can only restore a database backup using the same or a newer version of SQL Server.
There is however a workaround to this restriction. The workaround consists of scripting database and contents and importing the scripts using sqlcmd.
Here’s my scenario:
A database is being developed on SQL 2008 [A], and goes live on SQL 2008 R2 [B]. There is no problem copying the database from one system [A] to the other [B].
Then an error occurs, or something must be debugged or analysed, but developers only have SQL 2008 installed.
A newer version is available [C], but not on the develop machines.
So the SQL Server versions can be summarized as: [A] < [B] <= [C]
And this is the workaround:
- Create a full database backup on [B]
- Restore the backup in a new database on [C]
- In SSMS, locate the new database, and right-click
- Tasks/Generate Scripts
First, we create the DDL script
- Save scripts to a specific location
- Save to file: Single file, Save As: Unicode text
Advanced:
- Generate Script for Dependent Objects: True
- Schema qualify object names: True
- Script Collations: if you need them
- Script Defaults: True
- Script CREATE
- Script for Server Version: select version for [A]
- Script Logins, Owner, Permissions, etc: as required
- Types of data to script: Schema only
- Script Check Constraints, Foreign Keys, Full-Text Indexes, Indexes, Primary Keys, Triggers, Unique Keys: True
Next, Next until DDL file is being generated
Next, we create the Data script. Same steps as above, but
- Types of data to script: Data only
This may take a while…
In the meantime, we can edit the original DDL file. Open the file (I’ll refer to it a script.sql) in a Unicode-capable text editor.
Since we want to create the database on a dev machine, I tend to skip the DDL statements creating the database:
- Starting from USE [master] up to the [USER <mydatabase>]. Depending on the contents of the database, you may also want to skip the CREATE USER and CREATE ROLE statements.
- Eventually, you’ll find the first CREATE (PROCEDURE, TABLE, VIEW, etc.) statement. This is where is gets interesting for us.
- This CREATE section lasts until you find the first CREATE INDEX or ALTER (TABLE) statement.
- Copy this section to a new file, as save it (say, script1.sql).
- In script.sql, select from the first CREATE INDEX or ALTER statement down to the last statement before USE [master]. Save this section as script2.sql.
We end up having these files:
- script.sql .. the original Schema-only file
- script1.sql .. the CREATE statements
- script2.sql .. the indexes and constraints
- data.sql .. the original Data-only file
The DDL scripts are typically not so large, and can be executed from inside SSMS. The Data file may be more problematic, as even a small database can generate a huge (several GB!) data SQL file.
If SSMS is capable of handling all the SQL files, the correct sequence is:
- script1.sql .. the CREATE statements
- data.sql .. the original Data-only file
- script2.sql .. the indexes and constraints
Why do we split the original Schema-only file? Well, the schema defines constraint, such as foreign keys. Typically, the generation of the data script does not take care of such foreign key constraints. That’s why we apply the constraints only after adding the data.
What to do if the data file is too big for SSMS? We’ll run it from the command line:
Open the command prompt (Win+Run, cmd.exe) and try to connect to your SQL Server [A] using sqlcmd. Type sqlcmd -? to get the list of options.
If you have Windows authentication enabled and SQL Server runs on your local machine, the statement
sqlcmd -d [mydatabase] -Q "SELECT 1"
verifies you can connect to SQL Server. If successful, the expected result will be displayed:
-----------
1
(1 rows affected)
Use the -i command line switch to declare the data.sql as input file:
sqlcmd [all other switches] -i c:\path\to\data.sql
Again, this command may be busy for quite some time. If you have no idea what is going on, and really really want to know, use this script to watch the inserts from SSMS.
devioblog 