How to restore Sql server database backup and recovery plan 1

In real world, data is very much important to the client and Database administrator is responsible for protecting its client’s database by having strong database backup strategy plan. Database backup plays very important role at the time of recovery when disaster strikes. Recommended to keep your database backup more than one place for very critical system, we are using TSM (Tivoli Storage manager) server to store the database backup files on remote location.

Database Backup strategy plan is pure depends on client or organization how much data they can lose in disaster. If you are working on OLTP application where database server deals with heavy transactions and client doesn’t want to lose any data then is better to go for the high availability solutions like Clustering, database mirroring and log shipping.
As we already know about Database mirroring and log shipping concept in sql server.
Before going for the database backup, we should know the recovery model of the database.

Recovery models in sql server 2005

SIMPLE – It doesn’t allow taking transaction log backup, it truncates log while taking full database backup. Simple recovery model it use, normally for none critical databases where transaction logs are not that much important to the client. Also use for the reporting purpose on secondary server.

FULL – It supports all the type of backup in sql server 2005. It plays important role while performing the point in time recovery. On critical Online Transaction Processing databases are uses the FULL recovery to protect the data. It acts as best option to recover data on same point in time.

BULK LOGGED – To capture bulk operation related details, we use to set the recovery model as Bulk Logged to log some additional details.

Most Commonly used recovery models are SIMPLE and FULL. Taking decisions to set recovery model SIMPLE or FULL is purely on business or client. Now time to know about database backup to support the organization plan strategy.

Different types of database backup options are available in SQL Server 2005.

Full Backup
It works with all recovery models in sql server, it creates the complete copy of the database at that point of time. So if any disaster occurs we can go to the old data by using these full backup files. It acts base for all other backup without restoring full backup, will allow to restore the others backups like TLOG backup and differential backup.

 

Now Question is How to take database backup? Answer is we can use below command to take full database backup on disk.

BACKUP DATABASE DEMODB
TO DISK = ‘D:\SQLBACKUPS\DBFULL.bak’

We can use below to change the database recovery model to SIMPLE.

ALTER DATABASE DEMODB SET RECOVERY SIMPLE

Differential backup

It used as incremental backup for database, generally in the DBA deals with more than one server in workplace with different size of databases let say a database is having 100GB in size. While taking full backup on business hours it slows down the server which will affect to the production. we decided to use differential instead of full backup, it will capture all details after the last full backup.
So we scheduled differential backup every day at midnight and full backup in weekends to avoid any issues on our heavily loaded production server.

BACKUP DATABASE DEMODB
TO DISK = ‘D:\SQLBACKUPS\DBFULL.bak’
WITH DIFFERENTIAL

While restoring differential backup, first restore full backup otherwise it will give below error

Msg 3117, Level 16, State 1, Line 3
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Steps involve in restoring database

1. Restore the full backup acts as base for differential and transactional log backup

2. Differential backup

Transaction Log Backup

It is use to keep the track of transactions on OLTP application environment where transaction are very critical and client doesn’t want to lose it. Currently we are taking differential database backup every day if anything happens to the production or due to some failure production goes there is possibility to lose transaction data in between backups. So to protect the transaction data, we scheduled the transaction log backup after every 30 minutes by doing we can minimize the lose.

Transaction log backup is possible with FULL and BULK Logged recovery model.

BACKUP LOG OnlineStoreDB
TO DISK = ‘D:\SQLBACKUP\OnlineStoreDB_log.trn’

To restore the transaction log backup, we have to follow below steps

Restore Full database backup (Leave the database in the restoring mode to restore differential)

Differential database backup (Leave the database in the restoring mode to restore transaction log)

Transaction Log backup with recovery

To more details about Transaction log and its architecture.
We should also validate our database backup on alternative days so that to feel confident enough to restore back without any issues. Based on strong database backup strategy, we can recover database in less time frame.

One comment on “How to restore Sql server database backup and recovery plan

  1. Reply Film Telecharger Sep 2, 2015 10:05 pm

    I am actually pleased to glance at this web site posts which contains tons
    of helpful facts, thanks for providing these kinds
    of statistics.

Leave a Reply

  

  

  


6 + = 10