SQL 2008 Restore Database

Leave a comment (1) Go to comments

STEP by STEP GUIDE for SQL 2008 Restore Database from a FULL BACKUP | Differential backup | Transaction Log Backup and Point in Time Restore

For better understanding, let’s assume, we have following backup files, where we have FULL backups , Differential backups and Transaction LOG backups.

DAY Date Type Of Backup
Sunday 2012-02-12 Full Database Backup
Monday 2012-02-13 Transaction Log Backup
Tuesday 2012-02-14 Transaction Log Backup
Wednesday 2012-02-15 Differential Database Backup
Thursday 2012-02-16 Transaction Log Backup
Friday 2012-02-17 Transaction Log Backup
Saturday 2012-02-18 Transaction Log Backup
Sunday 2012-02-19 Full Database Backup

as shown below


SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

where BACKUP is schedule to run on close of business day which after 12:00 midnight.

To make it more visualize and clear understanding, I have created a test table named Backup_TEST and inserted a record on daily basis, which means

  1. record 1 was inserted 2012-02-11 during day and this data was backup up on 2012-02-12 12:07 AM
  2. record 2 was inserted 2012-02-12 during day and this data was backup up on 2012-02-13 12:07 AM and so on…

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

Let’s PREFORM Restore Database for SQL Server 2008 environment considering various CASES

NOTE – These instructions and STEP remain same across all SQL Server environments including SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012

CASE 1 - RESTORE database in SQL 2008 from a FULL Database Backup

In this case, let’s assume that we want to restore a database back to state which was on 2012-02-12. Thus in this case, we need to perform a single restore that is full backup database backup which was created on 2012-02-12 12:07 AM and backup media file name was “CRM-12Feb2012.BAK”

STEP by STEP GUIDE to RESTORE SQL Database from CRM-12Feb2012.BAK file

  1. Connect to SQL Server Instance using SQL Server Management Studio
  2. Right click on database >> Select Restore Database
  3. In database Restore Database Property Page
    • Enter Database NAME, this could be same as previous or different (Restore Database AS ”)
    • Locate a backup file
    • Click Options, in case you want to move database files to some other location, in my case, I am not changing this.
    • Click OK, this will restore a database for you

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

In case you, want to schedule this restore during off hours, please click SCRIPT Button, which is the TOP, this will create a TSQL Statement for this restore operation.

Instead of GUI, we can use the following TSQL Script to complete this Restore Operation.

RESTORE DATABASE [CRM] FROM  DISK = N'C:\MSSQL\Backup\CRM-12Feb2012.BAK'
WITH  FILE = 1,
NOUNLOAD,  STATS = 1 -- this will show you current status in %
GO

Script OUTPUT

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

Verification of Restore Data – Once the Restore Database operation is completed from a Full backup following data is returned in select statement, only one record, which is expected.

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

This is HOW we restore sql database from a full database backup.

CASE 2 - RESTORE Differential backup

In this case, let’s assume that we want to restore a database back to state which was on Wednesday, 2012-02-15. Thus in this case, we need to perform

  1. Restore full backup database backup which was created on 2012-02-12 12:07 AM and backup media file name was “CRM-12Feb2012.BAK” with RESTORE with NO RECOVERY
  2. Restore Differential backup, database backup which was created on 2012-02-15 12:12 AM and backup media file name was “CRM-15Feb2012.DIFF”

STEP by STEP GUIDE to RESTORE SQL Differential backup

  1. Connect to SQL Server Instance using SQL Server Management Studio and then Right click on database >> Select Restore Database (same as CASE 1)
  2. In database Restore Database Property Page
    • Enter Database NAME, this could be same as previous or different (Restore Database AS ”)
    • Locate a backup file
    • Click Options, and change Recovery State for this restore operation to “RESTORE with NO RECOVERY“, this will allow up to perform later restores
    • Click OK, this will restore a partial database for you, Once this is completed, we will not be able to access the database as we also need to restore differential backup too

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

    • Click OK, this will restore a partial database for you, Once this is completed, we will not be able to access the database as we also need to restore differential backup too

Instead of GUI, we can use the following TSQL Script to complete this Restore Operation.

RESTORE DATABASE [CRM]
FROM  DISK = N'C:\MSSQL\Backup\CRM-12Feb2012.BAK'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 1
GO

Screen OUTPUT

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

    • Now we need to RESTORE Differential backup which was created on 2012-02-15 12:12 AM and backup media file name was “CRM-15Feb2012.DIFF”

This Restore can be performed either via SSMS or using the following TSQL CODE.

RESTORE DATABASE [CRM]
FROM  DISK = N'C:\MSSQL\Backup\CRM-15Feb2012.DIFF'
WITH  FILE = 1,  NOUNLOAD,  STATS = 1
GO

SCRIPT OUTPUT

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

Once the SQL Server Differential restore backup is complete SQL Server Database ‘CRM‘, woulbd be become available, and if we run a query against our test table, we will get the following data, which is expected.

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

NOTE - There was no need to restore Transaction log backups as differential backup copy hold all the data, which was being modified since last full backup.

CASE 3 - SQL RESTORE from Transaction log backup without a Differential

In this case, let’s assume that we want to restore a database back to state which was on Tuesday, 2012-02-14. Thus in this case, we need to perform

  1. Restore full backup database backup which was created on 2012-02-12 12:07 AM and backup media file name was “CRM-12Feb2012.BAK” with RESTORE with NO RECOVERY
  2. Restore Transaction log backup, database backup which was created on 2012-02-13 12:09 AM and backup media file name was “CRM-13Feb2012.trn” with RESTORE with NO RECOVERY
  3. Restore Transaction log backup, database backup which was created on 2012-02-14 12:10 AM and backup media file name was “CRM-14Feb2012.trn” with RESTORE with RECOVERY

Note- Last restore operation should be with recovery to make database accessible

STEP by STEP GUIDE to RESTORE SQL transaction log backup

      1. Connect to SQL Server Instance using SQL Server Management Studio and then Right click on database >> Select Restore Database (same as CASE 1)
      2. In database Restore Database Property Page
        • Enter Database NAME, this could be same as previous or different (Restore Database AS ”)
        • Locate a backup file
        • Click Options, and change Recovery State for this restore operation to “RESTORE with NO RECOVERY“, this will allow up to perform later restores
        • Click OK, this will restore a partial database for you, Once this is completed, we will not be able to access the database as we also need to restore differential backup too

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

      • Click OK, this will restore a partial database for you, Once this is completed, we will not be able to access the database as we also need to restore transaction log backup too

3.  Restore Transaction log backup, database backup which was created on 2012-02-13 12:09 AM and backup media file name was “CRM-13Feb2012.trn” with RESTORE with NO RECOVERY

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

 

 

4 . Restore Transaction log backup, database backup which was created on 2012-02-14 12:10 AM and backup media file name was “CRM-14Feb2012.trn” with RESTORE with RECOVERY

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

Once this is restored, Database would be accessible,  if we run a query against our test table, we will get the following data, which is expected.

SQL 2008 Restore Database sql server restore database with recovery restore database sql restore database in sql server 2008 database restore sql server

CASE 4 - SQL RESTORE from Transaction log backup with Differential

To cover this, let’s assume that we want to restore a database back to state which was on Friday , 2012-02-17. Thus in this case, we need to perform

  1. Restore full backup database backup which was created on 2012-02-12 12:07 AM and backup media file name was “CRM-12Feb2012.BAK” with RESTORE with NO RECOVERY
  2. Restore Differential backup, database backup which was created on 2012-02-15 12:12 AM and backup media file name was “CRM-15Feb2012.DIFF” with RESTORE with NO RECOVERY
  3. Restore Transaction log backup, database backup which was created on 2012-02-16 12:13 AM and backup media file name was “CRM-16Feb2012.trn” with RESTORE with NO RECOVERY
  4. Restore Transaction log backup, database backup which was created on 2012-02-17 12:14 AM and backup media file name was “CRM-17Feb2012.trn” with RESTORE with NO RECOVERY
  5. Restore Transaction log backup, database backup which was created on 2012-02-18 12:15 AM and backup media file name was “CRM-18Feb2012.trn” with RESTORE with RECOVERY

Steps of restore is remain same , as shown in above scenarios.

This summarizes all SQL 2008 Restore Database cases

EOF - SQL 2008 Restore Database, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. Great very nice explain ….thanks a lot…

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.