Export a MS-SQL database to an earlier version of SQL Server

MS-SQL Server is not backwards compatible so you can’t backup a db on SQL Server 2008 R2 and restore on SQL Server 2008.

To get around this you need to export the schema and data as a script file.

  1. Open SQL Server Management Studio on the server which contains to the db you want to copy
  2. Select your source Database in object explorer
  3. Select Tasks>Generate Scripts.
  4. Select ‘Script entire database and all database objects’, press ‘Next’
  5. Select ‘Save to File’ and click on the ‘Advanced’ button
  6. Select ‘Script for Server Version’ and select the version you want: 200/2005/2008
  7. Select ‘Type of data to Script’ and select Schema/Data/both
  8. Click ‘OK’, Next

Now copy the script file to the target server.

If the source was a small db, this file shouldn’t be too big.   Try and open it in SQL management studio.   If this is possible, check the start of the script where the db is created.   Is this the name you want to use?   Is the db file and log file locations correct and their directories exist?   Make these changes as necessary.  Run the script.

If the file is too big to load in management studio, download a copy of 010 Editor: https://www.google.com.au/search?aq=0&oq=010&sugexp=chrome,mod=4&sourceid=chrome&ie=UTF-8&q=010+editor&qscrl=1

This program allows you to edit massive text based files.  Once installed, open the script file and make the modifications described above.

Now open a command prompt and type the following:

sqlcmd -S THE_NAME_OF_YOU_TARGET_DB_SERVER -i C:\path\to\script.sql -o C:\path\to\output.txt -x

Get a cuppa…it may take a while…

See here for more info: http://msdn.microsoft.com/en-us/library/ms180944.aspx

Note the -S flag is case sensitive.

Your windows account will need permission to create a database on master.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>