Tuesday, April 5, 2011

Restore MS SQL Server Backup file

Recently I was faced with a dilemma - a friend of mine had a M$ SQL Server backup file and wanted me to extract some data from it. Since I work on Ubuntu & LAMP stack, I had to grab hold of a Win7 laptop and then follow the below steps.

To restore a database you need to do three things:
  1. Install MS SQL Server Express
  2. Interrogate the backup file to find the logical file names it contains
  3. Restore the file into the appropriate database

Assume: For this example, the backup file is C:\Backups\DB_Backup_20110131.bak

Step 1: Install MS SQL Server
Go to Micro$oft site using the direct link http://www.microsoft.com/express/Database/I-nstallOptions.aspx and download and install the most appropriate version of SQL Server Express.

Step 2: Interrogate Backup File
RESTORE FILELISTONLY FROM DISK = 'C:\Backups\DB_Backup_20110131.bak'

This will return something like the below set of rows which represent the internal logical composition of the backup file:

LogicalName        PhysicalName                 Type FileGroupName Size      MaxSize
------------------ ---------------------------- ---- ------------- --------- --------------
SourceDatabase_data C:\SqlServer\Src_DB_Data.mdf D    PRIMARY       836461765 35184372080640
SourceDatabase_log  C:\SqlServer\Src_DB_Log.ldf  L    NULL           91592723 35184372080640

Step 3: Restore from Backup File

FROM DISK = 'C:\Backups\DB_Backup_20110131.bak'
     REPLACE, -- Overwrite DB - if one exists
     RECOVERY, -- Use if this is the only file to recover
     STATS = 10, -- Show progress (every 10%)
     MOVE 'SourceDatabase_data' TO 'C:\SqlServer\Src_DB_Data.mdf', 
     MOVE 'SourceDatabase_log' TO 'C:\SqlServer\Src_DB_Data.mdf'

No comments:


Related Posts Plugin for WordPress, Blogger...