How test different restore scenarios?

Question

Post reply

How test different restore scenarios?

madhu-686862

SSChampion

Points: 10707
More actions
October 24, 2008 at 6:45 pm

#195070

hi,
We created some backup scripts for full,differential and log backups. The backups of databases is fine. But I need to
Test different restore scenario's from the backup and log files.I need to do this in test environment. So far iam able to restore full, diff and log backups
Please advice me to acheive the following scenarios?
1.If the mdf or ldf file is corrupted what are the steps to restore? is it possible to get the data back?
2.If .bak or .trn file corrupted what are the steps to restore?
like this type of scenarios, I need to test could you plz advice me...
Thank you

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

aravind_0404 Say Hey Kid Points: 680 More actions · Answer 1

in the first case if your mdf is corrupted then do a

full back up restore

then tranlog backuprestore

then do the point in time recovery using your taillog backups.in case of the tail log backups if your recovery model is full mode then you can restore the data from the ldf if your mdf is corrupted. if your database is in the bulk logged recovery mode then both your mdf and ldf should be good.

in the second case if your .bak or .trn files are corrupted i think you can do nothing but to restore the data from the archived backups.(till that point you can restore your data)

madhu-686862 SSChampion Points: 10707 More actions · Answer 2

Thank You

what exactly meant by tail backups..is there any separate commands to take tail backups other than backup log db_name

when should we take the tail backups?

aravind_0404 Say Hey Kid Points: 680 More actions · Answer 3

usually for every hour you take your tranlog backups...if your data is corrupted in the mid of the hour like 1:30,something like that ..for that remaining half hour you take your tranlog backups called as tail log backup.for this in the SSMS GUI when you right click on the database you see tasks in the tasks you go to the backup in the backup pane you see an options tab in the select a page pane in the options page you see transaction log here you choose the second option this does your tail log backup. before this in the general page you need to select your backup type as transaction log.as only in the transaction log you can do your tail backups.

for T-SQL commands you need to check the books online.

all the best!!!

vkundar Hall of Fame Points: 3431 More actions · Answer 4

Thanks everyone !!

I am new to this forum.

I had faced the same backup issue and I followed the above steps and it was successfull.

Right now I have created a maintenance plan to backup FULL DB once in week and Tlog backup everyday.

Good to be with so many intelligent people.:D

Cheers,

Got an idea..share it !!

DBA_Vishal

homebrew01 SSC Guru Points: 55454 More actions · Answer 5

vkundar (10/27/2008)
Thanks everyone !!
I am new to this forum.
I had faced the same backup issue and I followed the above steps and it was successfull.
Right now I have created a maintenance plan to backup FULL DB once in week and Tlog backup everyday.
Good to be with so many intelligent people.:D

How many times per day is your T-Log backup ? if it's a production system, then most people recommend at least once per hour during business, or even more frequently. Since your FULL is only once a week, you could also consider running a Differential(s) during the week. Then, if you need to restore, you will only need the T-Logs after the most recent DIFF.

madhu-686862 SSChampion Points: 10707 More actions · Answer 6

Hi,

Iam stuck in testing the restore scenarios. I need to generate the scenarios in developement and document. If anybody have some info on this plz help me

AAkula SSCertifiable Points: 5394 More actions · Answer 7

anybody has generated n tested the possible restore scenarios? Iam also looking for this information.

thx much

madhu-686862 SSChampion Points: 10707 More actions · Answer 8

Hi All,

Iam trying to test different restore scenarios

from my backups and document them, inorder to recover the data in disaster situations.

So for this purpose I created a script which inserts data continuosly from one table to the other in the same database to generate the log.

Declare @total bigint

Declare @total1 bigint

DECLARE @DATE DATETIME

SET @DATE = GETDATE()

SELECT @TOTAL = COUNT(*) FROM dbo.TEST1

WHILE @DATE < GETDATE()+1

BEGIN

INSERT INTO dbo.TEST

SELECT * FROM dbo.TEST1

SELECT @TOTAL1 = COUNT(*) FROM dbo.TEST

WAITFOR DELAY '00:00:01:00'

IF @TOTAL1 > (@TOTAL*5)

TRUNCATE TABLE dbo.TEST

END

Backup plan:Full back at midnight,diff backup twice a day n log backup every 15 mins. Now I need to test the different restore scenarios.

could plz anybody give me some scenarios that we can perform??

I did one scenario like the below:

1. I ran the script for 4min. The test1 table has 2571 rows. So after 4 min the no.of rows in test is 10284.

2.took the log backup

3.delete the TEST table

3.then restore the full,diff n log,I can see the test table with row count 10284.

So I got the total data back..

Like this what are the possible scenarios I can test?

Plz advice me???

Thanks in advance..

madhu-686862 SSChampion Points: 10707 More actions · Answer 9

can any body plz advice me some scenarios to test....

In Oracle we have test cases like

if datafile is deleted go for this method to restore

if database corrupted go for this method to restore like do we have any test cases for sql server if So could you plz give me some document with different restore scenarios

Thanks

madhu-686862 SSChampion Points: 10707 More actions · Answer 10

hi,

I developed a test case for restore scenario. Plz advice me like the scenario here what are the other possible scenarios we can build.

Scenario : How to recover the data to the point in time.

Aim:

We have a full backup every day, differential at every 4hrs and log backups every 15 mins.A table is deleted at a particular known time.

We need recover that table to the known point in time.

Description:

We have a full backup at 2:00am, differential at 6:00am and log backups every 15 mins from 6:15am to 9:15am.From 9:15am to 9:23am data is inserted into TEST table from CodeGenerators in Policy_PROD database. No more data is inserted into the test table after 9:23am.The row count in test table at 9:23am is 17997.

At 9:24am the table is deleted. Now we need recover the TEST table with row count 10000.

Solution:

Take the tail log backup at 9:24am immediately after deleting the TEST table.

Here is the Restore sequence:

Step1: Restore the 2:00am full backup:

RESTORE DATABASE [Policy_PROD]

FROM DISK = N'F:\BACKUPS\Policy_PROD\Policy_PROD_11-06-2008.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

Step2: Restore the 6:00am Diff backup:

RESTORE DATABASE [Policy_PROD]

FROM DISK = 'F:\BACKUPS\Policy_PROD_Diff\Policy_PROD_20081106060000.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

Step3: Restore the log backups starting from 6:15am to 9:15am:

RESTORE LOG [Policy_PROD]

FROM DISK = N'F:\BACKUPS\Policy_PROD_Log\Policy_PROD_Log20081106061500.trn'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

.

RESTORE LOG [Policy_PROD]

FROM DISK = N'F:\BACKUPS\Policy_PROD_Log\Policy_PROD_Log20081106091500.trn'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

Step4: Restore the Tail log backup taken at 9:24 am.

RESTORE LOG [Policy_PROD]

FROM DISK = N'F:\BACKUPS\Policy_PROD_Log\Policy_PROD_Log1.trn'

WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'11/06/2008 09:24:00'

GO

Result: The TEST table is recovered and the row count is 10000.