Restore SQL Server 2012 database to SQL 2008

I did these steps in 2013-11 to transfer a SQL Express 2012 database to SQL Express 2008 (R2 I think). This is from a live website to the development server that I didn't want to update to 2012.

 

Overview: Script the database Objects and restore them on another computer

 

From MSSMS

- To script the database objects you can use Generate Scripts Task.
- Generate Scripts Task can be found in SQL Server management studio.
- Right Click on the database -> Click On Tasks -> Then Click on Generate Scripts
- Run through the Wizard.

IMPORTANT: In the set scripting options tab in the generate scripts wizard click on ADVANCED and select the following options:

- For ‘types of data to script’ option Select ‘Schema and data’
- For ‘Script for version’ select the required target SQL Server version

Generate the scripts and run the generated scripts on the destination server. e.g. with osql -E -i file.sql

 

Note:

- the SQL file starts with a CREATE DATABASE ... line.
- the DATABASE NAME IS EMBEDDED in the SQL file. (And the entire file is processed before it gives you the error... hours later...). I.e. database name must be the same on new and original servers.
- the database file PATH is also embedded. Either edit the enourmous SQL file, or make sure the same path exists on the computer doing the "restore".

 

You may have to run the script multiple times when you face any error.

Create a new database every time you rerun the script.

Fix the errors that you may face due to order in which objects are created (or) data is inserted into tables different tables.

 

Loosely based on: http://sqlserverlearner.com/2011/restore-higher-version-database-backup-on-lower-version-sql-server-restore-problem

 
tags: backup, restore, sqlserver
Related Scribbles:
  • Microsoft SQL Server Backup and Migration Scripts
  • Backup Tricks and Links
  • SQL Server


  • ID: 981
    Author:
    leonard
    Date Updated:
    2015-05-14 17:34:07
    Date Created:
    2013-11-05 10:56:44

    Edit

    Comments?
     >> Leonard Chan's Homepage  >> Scribble Web  >> Restore SQL Server 2012 database to SQL 2008
    leonard.lotus-land.ca is hosted by Perceptus Solutions Inc.