Thread: MS SQL Server General Questions/restoring a backup from sql server 2008 to 2005

restoring a backup from sql server 2008 to 2005

www.sqlservercentral.com/Forums/Topic584539-357-1.aspx


 


If you need to copy the database from sql server 2008 to sql server 2005, I found another way to copy the database by creating a script.



Steps:



To create the scripts, run the "Generate SQL Server Scripts" wizard in SQL Server Management Studio by right clicking on the database and selecting "Tasks –> Generate Scripts."



It shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the database on SQL Server 2008 and restore it to SQL Server 2005:



Click "Script all objects in the selected database", and then click "Next."



Change the following script options:

set "Script for Server Version" to "SQL Server 2005"

and set "Script Data" to "True".



If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." And Click "Next"



Select "Script to file"

Select the file name

and choose "Single file"

Click "Next" for a summary



Now click on "Finish" to get progress messages while the script runs and completes

If the generation process fails, then you can use the "Report" option to see why.

When the scripting is completed, look for the following lines:



CREATE DATABASE [Northwind] ON PRIMARY



(NAME = N'Northwind', FILENAME =

N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.mdf' ,

SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )



LOG ON



(NAME = N'Northwind_log', FILENAME =

N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.ldf' ,

SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)



GO

You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:



--EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'

--GO

Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment.