Restore database backup in sql server 3

SQL Server gives lots of option to restore the database in case of any disaster. As a DBA, we have to make each possible effort to avoid data loss in such a critical situation. Best practice to restore the database using backup. SQL Server provides different type of backup options for database restoration; those are Full backup, Transaction Log Backup and Differential backup.

To know more about sql server database and restore
We must have very good backup plan for production environment to recover in case of failure.
Point in time recovery is available in sql server 2005 which will allow you to recover your database at particular time. We will go through the example to understand disaster recovery on sql server database.

 

Lets say we have database backup schedule on every night around 12 AM and differential backup every 4 hours with transaction log backup every 30 minutes as per business standard for minimal data loss.

 

Let’s say, application team has raised request for executing Sql adhoc query on production environment with all necessary approvals. As a part daily routine we have executed the query on production server.

 

After executing the query, team came to know that wrong data got updated on production server on 1.40 PM, now disaster strike DBA has to go for the recovery option available with him. First thing we have to do is taking a transaction log backup.

This incident has occurred on 1.15 PM as per execution time recorded.
As per backup plan, we have last full backup which was done at midnight around 12 AM.
Look for the differential backup and transaction log backup available on tape. On 12 o’clock latest differential backup was done and transaction log backup at 1.30 PM.

 

Question is, How to recover database with available backup?

 

For recovery of any database follow the below steps
1. First thing, we have to do is bring database offline with restricted user mode

ALTER DATABASE Salesdb
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE
2. Restore full backup on production server.

RESTORE DATABASE Salesdb

FROM DISK = ‘D:\DBBackup\SalesBackup.bak’

WITH NORECOVERY, RESTRICTED_USER
3. Latest differential backup

RESTORE DIFFERENTIAL Salesdb

FROM DISK = ‘D:\DBBackup\SalesBackup.bak’

WITH NORECOVERY, RESTRICTED_USER

 

4. Apply the transaction log backup

RESTORE LOG Salesdb

FROM DISK = ‘D:\DBBackup\ SalesdbBackupLog.trn’ WITH RESTRICTED_USER,

STOPAT = ‘2012-04-22 1:12:44.00’, RECOVERY
5. Bring back the database in Multi User mode

RESTORE DATABASE Salesdb
FROM DISK = ‘D:\DBBackup\SalesBackup.bak’ WITH NORECOVERY, RESTRICTED_USER

 

6. Now we can check the Data which was affected due to adhoc query executed by application team.

 

Select * from Salesdetails (Check out the count and records details)

 

We have recovered database before the time of disaster, now to avoid data loss we are creating database snapshot on test server so that we can recover data on that point in time.

 

USE master
GO
CREATE DATABASE Salesdb_Snapshot ON (NAME = ‘ Salesdb ‘,
FILENAME = ‘D:\DBBackup\ Salesdb_Snapshot.snap’) AS SNAPSHOT OF [Salesdb]
GO

3 thoughts on “Restore database backup in sql server

  1. Reply Harrison Coccia May 6, 2013 6:02 pm

    Data backup online is not too hard. My computer documents are all encrypted at backup Their cloud is the fastest and also free.

  2. Reply google api maps Nov 30, 2015 6:25 pm

    I always spent my half an hour to read this web site’s content all the time along with a cup of coffee.

  3. Reply google api maps Nov 30, 2015 10:37 pm

    Oh my goodness! Incredible article dude! Thanks, However I am going through problems with your RSS.
    I don’t understand why I can’t join it. Is there anybody else having
    the same RSS problems? Anybody who knows the answer can you kindly respond?

    Thanx!!

Leave a Reply

  

  

  


8 + = 17