GAPTHEGURU

Geek with special skills

Restore Database From SQL Server 2008 to SQL Server 2005 Part 1 – 3

PART 1:

Problem

When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or SQL Server 2000, you will see some error messages as the examples below.

Backup and Restore

You have backup a database from SQL Server 2008. If you try to restore the backup database file to SQL Server 2005, you will receive the error message:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information:
-> The media family on device ‘the backup file‘ is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

Restore Database Error Message

Detach and Attach

You have detach a database from SQL Server 2008. If you try to attach the detached database file to SQL Server 2005, you will receive the error message:

Attach database failed for Server ‘SQL Server name’. (Microsoft.SqlServer.Smo)
Additional information:
-> An exception occurred while executing a Transact-SQL statement batch. (Microsoft.SqlServer.ConnectionInfo)
–> The database ‘database name’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
Could not open new database ‘database name’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

Attach Database Error Message

Solution

These problems occur because a backup or detach database file is not backward compatible. You cannot restore or attach a database which is created from a higher version of SQL Server to a lower version of SQL Server.

But there are some alternatives which can help you to restore a database to a lower version of SQL Server. I divide into separate parts.

  1. Part 2: Generate SQL Server Scripts Wizard. The solution creates a SQL Server script file using a wizard. Then, you simply execute the script file on SQL Server 2005 or SQL Server 2000. So you will get everything as same as the source database on the destination. But there are some disadvantages:
    • If the source database contains lots of data, you will have a large script file.
    • The generated file is a plain text. Anyone who has access to the file can read it. So you should delete the script file after the restoration.
  2. Part 3: Import and Export Wizard. This solution exports data to the targeted SQL Server using a wizard. It is more secure and effective than the first solution. But you can only export tables and views only.

————————————————————————————————————————————————————————————————————————————————————————————

PART 2: Generate SQL Server Scripts Wizard

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

  1. 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.
    Generate Scripts
  2. On Welcome to the Generate SQL Server Scripts Wizard, click Next.
    Welcome to the Generate SQL Server Scripts Wizard
  3. On Select Database, select Northwind and check Script all objects in the selected database. Then, click Next.
    Select Database
  4. 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.
    Script for Server Version to SQL Server 2005
  5. 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.
    Script Data to True
  6. On Output Option, select a destination for the output script. Select Script to file and browse to the location that you want. Click Next.
    Select Script Output Destination
  7. On Script Wizard Summary, you can review your selections. Then, click Finish.
    Script Wizard Summary
  8. On Generate Script Progress, the wizard is creating a SQL Server script.
    Generate Script Progress
  9. When the script has been completed, you see the output file as similar the figure below.
    The Generated Script
  10. 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.
    Create New Database
  11. Type ‘Northwind’ as database name. Click OK.
    Create Database Northwind
  12. Execute the SQL Server script file that you have created.
    Execute the script
  13. Now the database ‘Northwind’ is restored on SQL Server 2005.
    Northwind Database

———————————————————————————————————————————————————————————-

PART 3: Export Data Wizard

On this post, you see how to export tables on ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 using export data wizard.

Step-by-step

  1. On Microsoft SQL Server Management Studio, connects to SQL Server 2008. Right-click on the database that you want to export data -> select Tasks -> Export Data.
    Export Data
  2. On Welcome to SQL Server Import and Export Wizard, click Next.
    Welcome to SQL Server Import and Export Wizard
  3. On Choose a Data Source, select the source from which to copy data. Set Data source to SQL Server Native Client 10.0. Verify that Server name is the source of SQL Server 2008 that you want and select Database as ‘Northwind’. Click Next.
    Choose a Data Source
  4. On Choose a Destination, specify where to copy data to. Set Destination to SQL Server Native Client 10.0. Type the Server name to the destination of SQL Server 2005 that you want. You can also click Refresh to verify if you can connect to the specify server name. Currently, I don’t have ‘Northwind’ database on SQL Server 2005 so I will create a new one, click New.
    Choose a Destination
  5. On Create Database, type name as ‘Northwind’ and click OK.
    Create Database
  6. Back to Choose a Destination, I have created ‘Northwind’ database so select it as Database. Click Next.
    Choose a Destination
  7. On Specify Table Copy or Query, select Copy data from one or more tables or views and click Next.
    Copy data from one or more tables or views
  8. On Select Sources Tables and Views, select tables that you want to export. On this example, I select all tables on ‘Northwind’ database.
    Select Sources Tables and Views
  9. On Save and Run Package, click Next.
    Save and Run Package
  10. On Complete the Wizard, you can verify the choices made in the wizard. Then, click Finish.
    Complete the Wizard
  11. Wait until the wizard finishes execution.
    Export Data Successful
  12. Now I have exported tables of ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 successfully.
    The Exported Tables
Advertisements

04/24/2012 Posted by | Sql Server, T-SQL | , , | 2 Comments

How to backup and restore database on Microsoft SQL Server 2005

Step-by-step

Backup a database.

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

  1. Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
  2. Right-click on the AdventureWorks database. Select Tasks -> Backup…
    Backup a SQL Server database
  3. On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
    1. Database – a database that you want to backup.
    2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
    3. Name – Name of this backup, you can name anything as you want.
    4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    5. Click OK to proceed backup.

    Select source and destination to backup

  4. Wait for a while and you’ll see a pop-up message when backup is finished.
    Backup success pop-up message
  5. Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    The backup file from SQL Server 2005 Server

Restore the database.

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

  1. Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
    The backup file
  2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
  3. Right-click on Databases. Select Restore Database…
    Restore the database
  4. Restore Database window appears. On Source for restore, select From device and click […] buttton to browse file
  5. Select source device
  6. On Specify Backup, ensure that Backup media is “File” and click Add.
    Select the backup media to restore
  7. On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
    Select the backup file to restore
  8. Back to Restore Database window.
    1. On Destination for restore, select “AdventureWorks”.
      Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
    2. On Source for restore, check the box in front of the backup name (in Restore column).
    3. Click OK.

    Select a destination database to restore

  9. Wait until restore finish and there’ll be a pop-up message notify.
    Restore success pop-up message
  10. Now you’ll see the restored database on the destination SQL Server.
    The restored database on SQL Server 2005

04/24/2012 Posted by | Sql Server | , | Leave a comment

   

%d bloggers like this: