Restore a SQL Server 2008 Database into SQL Server 2005

Tuesday, 5 March 20130 comments

 If you've tried to back up a database in SQL Server 2008 and then restore it into SQL Server 2005, you know that the database backups are not backward compatible. However, with SQL Server 2008 Management Studio, you can script data and schemas in SQL Server 2005 mode. This 10-Minute Solution takes you through the steps to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005.
  


Transferring databases from SQL Server 2008 to SQL Server 2005 is not possible with standard backup and restore facilities.

Leverage the scripting wizard in SQL Server 2008 to script data as well as schemas in SQL Server 2005 compatibility mode.


Using the "Generate SQL Server Scripts" Wizard

The Northwind database is no longer shipped as part of the SQL Server installation, but you can download it from go.microsoft.com. The data is scripted as INSERT statements.

To create the scripts, you have to run the "Generate SQL Server Scripts" wizard, which you can run within SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on the database and selecting "Tasks –> Generate Scripts."

Figure 1 shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the Northwind database on SQL Server 2008 and restore it to SQL Server 2005:
Click to enlarge
Figure 1. Initial Dialog to Generate SQL Server Scripts Wizard: To create your scripts, you have to run the "Generate scripts" wizard.
  1. Click "Script all objects in the selected database" (see Figure 2), and then click "Next."Click to enlarge
    Figure 2. Select Database Dialog:Click the "Script all objects in the selected database" option.
  2. Amend the script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True" (see Figure 3). (SQL Server 2000 is also supported.) 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." Click "Next" when you are happy with the options.Click to enlarge
    Figure 3. Choose Script Options Dialog:Set the "Script Data" option to "True."
  3. Select "Script to file," select the file name, and choose "Single file" (see Figure 4). Click "Next" for a summary (see Figure 5).

    Figure 4. Output Option Dialog: Select "Script to file," select the file name, and choose "Single file."


    Figure 5. Script Wizard Summary: Review your selections for the wizard.


  4. Now click on "Finish" to get progress messages while the script runs and completes (see Figure 6).Click to enlarge
    Figure 6. Generate Script Progress Dialog: Click "Finish" and you will get progress messages.
    If the generation process fails, then you can use the "Report" option to see why.

  5. 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\DATA\northwnd.mdf' ,
     SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    
     LOG ON 
    
    (NAME = N'Northwind_log', FILENAME =
     N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\northwnd.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. You can now test data against SQL Server 2008 and SQL Server 2005.

source:http://www.devx.com
Share this article :

Labels

/Accountant (4) /DA (3) /Exam (3) /I.tax (1) /IPo (2) /Interest Calculator (4) /Leave (1) /ORDERS (1) /PM Gr-1 (8) /PM Gr.-1 (1) /Rail Reservation (1) /Railway (1) /Update-2 (1) /Update-3 (1) /tv (1) ACCOUNT PBS/PACS (1) ADHAR(UID) (3) ALLOWANCES (2) ANSWER KEY (1) Accountant/question Paper (1) Admit card/PM Grade1 (2) Allowances.Empoly News (1) BANKING (1) BOOK FOR DEPARTMENTAL EXA (3) BROADBAND (1) BSNL (1) CCBridge Vrs.6.0 (2) CGHS Health Tips Staffs and Welfare (3) COMPUTER AND HARDWARE (10) Clear Log Files........ (1) Computer Guidlines (5) Cricket (1) Current Afairs (1) Customer care news (8) DB - Analyzer (19) DEPARTMENTAL ORDER (4) Departmental New (1) Departmental News (71) Do software (1) Dop News /RMS (1) Dop News pension (1) Dte Order/Tfr.and Posting (1) ECS SOFTWARE (1) EVENT (1) Employees News (53) Employees News/Education (2) Empolyment News (8) Exam/Result (21) F (1) GDS (1) GPF (3) General Issue (2) General Knowledge (12) General Knowledge/cheque (1) Gold coins (3) HCL compaint (1) Hardware/security Tips (1) Health Tips (2) IMO (1) INTERNATIONAL ARTICLS WEB (1) INTRES (2) IPO (2) IPO SOLVED PAPER (1) IPO/ASPOs (15) IPS Web (1) Income tax (2) Interest Calculator (2) Internet tips (2) LGO PAPER (2) Leave Rule (2) MODEL PAPER (37) MODEL PAPER/IPO (19) MODEL PAPER/PM GR-1 (21) MOF_Rat (1) Mail (1) Meghdoot 7.0 (4) Mobile Tip (4) Money Gram IMTS (1) NEW (1) NEWS (41) NPS (5) Negative list (1) New Pension Scheme (2) News Railway (6) News Tax (1) News Tchnology (1) News:-Increament (1) OTHERS (35) Order (3) PA/SA (6) PLI and RPLI (7) PM Gr.-1 (1) POST BANK OF INDIA (3) POSTAL ABBREVIATIONS (1) POSTAL HISTORY (1) PS Group/question Paper (1) Pass Book Printer (1) Pension staffs and welfare (2) Point of Sale (6) Postal Information (4) Postal Insurance (1) Postal News (1) Postman (1) Postman Module (1) Postmaster (2) Postmaster Cadre (7) QUESTION PAPER2011 (1) QUESTION PAPER2012 (1) Qestion Paper/2012 (1) R LIST (1) R-Net (2) Recovery tips SQL (5) Recruitment PA/SA Form (3) Result (5) Result/IPO (2) Rulling and low (2) SAVING BANK (27) SB ORDER (8) SBCO (5) SERVICE TAX (1) SPEED NET (14) SPEED POST/ORDER (4) SQL (7) SUB ACCOUNT (3) SYLLABUS FOR PA/SA (1) Server/Backup (2) Solve Paper PM-1 (3) Solve Paper PS GR B (1) Staffs and Welfare (7) Study Materials (8) TDS (2) TOOL (6) Tech News (6) Tech tips and Tricks (13) Treasury (1) Trouble shooting (1) Union News (6) Update/Sanchay Post (15) WUMT POs (1) Window Server (1) Windows (4) Windows Tips (4) Windows xp (1) Workfow............ (1) budget (2) e Services (3) e services Meghdoot7 (3) eMO (3) eMO ResetRegistration (1) ePayment (1) latest software (6) oder/DA (5) operating (1) sanchay post (22) security Tips (1) syllabus /pmgr1 (1) upsc (1)
 
Support : Creating Website | Johny Template | Mas Template
Copyright © 2011. PURNEAHPO - All Rights Reserved
Template Created by Creating Website Published by Mas Template
Proudly powered by Blogger