SQLServerWiki

“The Only Thing That Is Constant Is Change”

You decide what backup strategy you want!

Posted by database-wiki on September 30, 2011

Server issue like hardware failure, abrupt power shutdown, disk related issue cause corruption in SQL Server database(s), so we always suggest you take a backup of the database and transaction log for safety and maintenance of the database.

Types of recovery model and advantages. Also types of backup.

Full :- Full database backup.

Diff :- Differential database backup.

Log :- Transaction log backup.

For ultimate recovery scenario we need to have Full + Diff + log strategy.

  1. Monday at 1 AM take a full database
    backup.
  2. Tuesday 5 AM take a differential database
    backup.
  3. Wednesday 5 AM take a differential
    database backup.
  4. Thursday 5 AM take a differential
    database backup.
  5. Friday 5 AM take a differential database
    backup.
  6. Saturday 5 AM take a differential
    database backup.
  7. Schedule a transaction log backup for
    every one hour for 24/7.

Disaster recovery for our scenario.

Suppose our server crashed at  2.15 PM on Wednesday.

First what we need to do is to take tail log backup.

How to take a tail log backup

Consider this scenario:

  • We have a full database backup at 8:00 AM
  • A Differential Backup Every hour.
  • Tlog backups every 1/2 hour.
  • The Database crashes at 12:15 PM

The restore procedure would be:

  • Take a Tail Tog backup. There are 2 Scenarios  with Tail Log backup:

1. If the database is Online:

Backup database
<DatabaseName> to Disk = ” With NORECOVERY

Here NORECOVERY is used to avoid
the transaction taking place on the DATABASE .

2. If the Database is OFFLINE:

Backup Database
<DatabaseName> to Disk = ” With CONTINUE_AFTER_ERROR OR NO_TRUNCATE

CONTINUE_AFTER_ERROR: is used if
the database is in suspect mode.

NO_TRUNCATE: When the database is
damaged.

  • Restore the FULL database backup with NORECOVERY
  • Restore the 12:00 PM Differential Backup with
    NORECOVERY
  • Restore the Tail Log backup with NORECOVERY
  • While Restoring the Tail Log if you want to stop at a particular time, use this:

STOPAT = N’2009-03-27T12:10:00′

Time is in 24 hour format.

  • RESTORE THE DATABASE WITH RECOVERY.

To Repro the above steps:

drop database back

Create database back

create table test(a int, b int)

insert into test values (1,2)

backup database back to
disk = ‘C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\back_db.bak’

insert into test values (1,2)

backup database back to
disk = ‘C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\back_df.bak’ with DIFFERENTIAL

insert into test values (1,2)

ALTER DATABASE [back] ADD
FILE ( NAME = N’back_test’,FILENAME = N’C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\back_test.ndf’ )

insert into test values (1,2)


Stop SQL Service —


Rename the back_test.ndf as back_test1.ndf —


Start SQL Server Service —

— The Database won’t be accessible —

backup log BACK to
disk = ‘C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\back_lg.trn’ with
NO_TRUNCATE

RESTORE DATABASE [back] FROM
DISK = N’C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\back_db.bak’ WITH
NORECOVERY

RESTORE DATABASE [back] FROM
DISK = N’C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\back_df.bak’ WITH
NORECOVERY

RESTORE LOG [back] FROM
DISK = N’C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\back_lg.trn’ WITH
NORECOVERY

RESTORE DATABASE BACK WITH RECOVERY 

Then we need to restore fullback taken on Monday and
restore differential backup taken on Tuesday and Wednesday at 5 AM and then
restore the transaction log backup till 2 PM also with NO RECOVERY specified.
Finally
RESTORE DATABASE BACK WITH RECOVERY
(tail log backup). Tail log will restore till 2.15 PM before the crash.

Screen Shots Below:

STEP 1


STEP 2


STEP 3


STEP 4


STEP 5


STEP 6


STEP 7


STEP 8


STEP 9


STEP 10

STEP 11


STEP 12


 STEP 13

 Stop worrying about corruption!

One Response to “You decide what backup strategy you want!”

  1. This website was… how do I say it? Relevant!!

    Finally I’ve found something that helped me. Thanks a lot!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: