Restore Database From SQL Server 2008 to SQL Server 2005, Part 2: Generate SQL Server Scripts Wizard
MS SQL Server, Windows August 14th, 2009Generate SQL Server Scripts Wizard
On Part 1: Introduction, I mentioned about error messages when you try to restore a database from SQL Server 2008 to SQL Server 2005. Now let’s see a first solution to solve the problems.
On this post, you see how to backup ‘Northwind’ database by generate a SQL Server script on SQL Server 2008. Then, restore the ‘Northwind’ database by execute the SQL Server script on SQL Server 2005.
Step-by-step
- On Microsoft SQL Server Management Studio, connects to the SQL Server 2008. Right-click on the database that you want to backup and select Tasks -> Generate Scripts.
- On Welcome to the Generate SQL Server Scripts Wizard, click Next.
- On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
- On Choose Script Options, set Script Database Create to False and Script for Server Version to SQL Server 2005.
Note: You can set Script Database Create to True if your source and destination for store database files are the same location.
- Continue on Choose Script Options, scroll down and set Script Data to True. Click Next
Note: Set this option to true to include data on each table to a script.
- On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.
- On Script Wizard Summary, you can review your selections. Then, click Finish.
- On Generate Script Progress, the wizard is creating a SQL Server script.
- When the script has been completed, you see the output file as similar the figure below.
- Connect to SQL Server 2005, create a new database. Right-click Database -> New Database.
Note: If you have set Script Database Create to True on step 4, you don’t have to create a database manually.
- Type ‘Northwind’ as database name. Click OK.
- Execute the SQL Server script file that you have created.
- Now the database ‘Northwind’ is restored on SQL Server 2005.
Related post
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 3: Export Data Wizard Export Data Wizard On Part 1: Introduction, I mentioned about error messages when you try to restore a database from...
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 1: Introduction Problem When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or...
- How to backup and restore database on Microsoft SQL Server 2005 Introduction This post will give you a brief introduction of SQL Server backup. As we know, SQL Server backup software...
Related posts:
November 19th, 2009 at 9:17 am
I tried it and upon execution of the sql in the 2005 server I got this error:
“…Not enough storage is available to process this command”. I still have enough HD space, does the error mean the RAM? Thanks.
November 25th, 2009 at 2:49 pm
Hi, iami
Do one of the following, then retry the command: (1) reduce the number of running programs; (2) remove unwanted files from the disk the paging file is on and restart the system; (3) check the paging file disk for an I/O error; or (4) install additional memory in your system.
Reference: Microsoft Technet
April 22nd, 2010 at 7:53 pm
I followed this procedure but i keep getting this error message:
Column dDate in object table1 contains type Date, which is not supported in the target server version, SQL Server 2005. (Microsoft.SqlServer.Smo)
can someone help me about this error? what will i do?
thanks
May 1st, 2010 at 1:00 am
Mark, the error you are getting this as 2005 doesn’t have the data type “date” These need to be changed to datetime or another data type of 2005
June 17th, 2010 at 4:11 am
I got the same message as IAMI(message 1) got .So I just generated DDL Scripts and used export utility to export the data.
August 11th, 2010 at 7:55 pm
I followed the steps above but was unable to open and execute the script file – did I miss something?
If I try to open the script file in either SQL Server 2008 (on a networked Windows NT machine) or 2005 (on an un-networked laptop running Windows Vista) I get the same error message which reads:
The operation could not be completed
with no other advice.
Do you have any suggestions as to what I should try next?
Cheers, Ali
August 19th, 2010 at 10:26 am
Hi, Ali
Have you check if your script was successfully created? Try to open it with notepad and compare with my example on the post.
September 20th, 2010 at 11:59 pm
Man pero yo no tengo el SQL-2008 y tbm tengo algun script! tan solo tengo la BD general !
December 4th, 2010 at 6:09 am
Please note that this example is scripting data. So some generated scripts will not open (Not enough storage is available to process this command). If this is the case, you will need to use SQLCMD.
February 5th, 2011 at 1:05 am
Thanks
April 16th, 2011 at 3:34 am
Thanks. Saved my bacon!
MS has changed the options for scripting schema and/or data. The option now reads: Type of data to script.
August 24th, 2011 at 3:06 pm
Hi sir,
when i restoring my database data from sql server 2008 to sql server 2005 i got an error like dbo._TmSpAfterUpdateAppObjLineOINM give me urgent reply sir.It gives when creating script for 2005 process in generate and publish script page.
why i got that error.If any know the solution send to my mail srinub@envisionesl.com.
August 26th, 2011 at 7:58 pm
Hi,
many many thanks for this great tutorial.
You helped me a lot !!!
January 5th, 2012 at 12:27 pm
Hi,
I got the same error(not enough storage) while opening the script. I have tried it by restarting the computer but still get the same error. please help me urgently.