SQL Server Performance

How to verify and restore copy from back up file to create a new database ?

Discussion in 'Contribute Your SQL Server Scripts' started by techbabu303, Oct 8, 2007.

  1. techbabu303 New Member

    How to verify and restore copy from back up file to create a new database ?
    Most of the times a developer or support professional had to restore database from file sent by a customer or client , so I thought of writing down this article to save some time for these guys to do it on there own instead of looking around for friendly DBA.
    Steps to do this are listed below with examples so that it is relatively easy to follow these instructions.
    Step 1 Check the database server version or software used to take the back up file.
    Open the query analyzer in SQL 2000 or SQL Management Studio in SQL 2005 , run the following query.
    RESTORE HEADERONLY
    FROM < backup_device >
    Check the values of columns SOFTWARE MAJOR VERSION, SOFTWARE MINOR VERSION and SOFTWAREVERSIONBUILD.
    Example :
    RESTORE HEADERONLY
    FROM DISK='C:BackupNORTHWIND.BAK'
    Output :
    For SQL 2000
    SOFTWAREVERSIONMAJOR SOFTWAREVERSIONMINOR SOFTWAREVERSIONBUILD
    8 ---- 760
    For SQL 2005
    SOFTWAREVERSIONMAJOR SOFTWAREVERSIONMINOR SOFTWAREVERSIONBUILD
    9 ---- 1399
    Step 2 Once the SQL server version of back up file is determined as above you need to restore it only on same major version of database, if you try to restore the backup taken in SQL 2005 you would get error as listed below.
    Server: Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device 'f:satfin59_bwa.BAK'. Device error or device off-line. See the SQL Server error log for more details.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    Step 3 Now get the logical path of backup file and then restore the database as listed below.
    To get values of logical path run the following command
    RESTORE FILELISTONLY
    FROM <BACK DEVICE >
    Example :
    RESTORE FILELISTONLY
    FROM DISK ='f:BACKUPNORTHWIND.BAK'
    Step 4: Place the values of column LOGICALNAME in the command to create a new database with back-up file as listed below.
    RESTORE DATABASE NORTHWINDTEST
    FROM DISK = 'c:BACKUPNORTHWIND.bak'
    WITH MOVE 'NORTHWIND_DATA' TO 'c:DATANORTHWINDTEST.mdf',
    MOVE 'NORTHWIND_LOG' TO 'c:sql est_dataNORTHWINDTEST.ldf'
  2. satya Moderator

    Very sensible approach of controlling the restore activity, keep it up.
  3. techbabu303 New Member

    Thx Satya...you gurus have been encouraging me on.

Share This Page