Solved

Help with Restore Scenario MSSQL

Posted on 2015-01-14
5
92 Views
Last Modified: 2015-01-27
Paramaters

SQLSERVER HOSTNAME  BIGDADDY1
INSTANCE NAME   BIGDADDY1
DB NAME   JOHNSON1
RECOVERY MODEL FULL

Current fictional time is jan 18 1300
Requested point of time recovery is jan 16 1800

Backups of database listed below
names are intuitive for what they contain

full1.bak jan 13 1100  
dif1.bak jan 14 1100
dif2.bak jan 15 1100
dif3.bak jan 16 1100
dif4.bak jan 17 1100
dif5.bak jan 18 1100
dif6.bak jan 19 1100
full2.bak jan 20 1100

tran1.trn   jan 11 1200
tran2.trn   jan 12 1200
tran3.trn   jan 13 1200
tran4.trn   jan 14 1200
tran5.trn   jan 15 1200
tran6.trn   jan 16 1200
tran7.trn   jan 17 1200
tran8.trn   jan 18 1200

current date is jan 20
Requested Recovery point time is jan 16 1800

I am trying to nail down the logic with recovery in time as far as the order in which to restore and how to restore.


is the following the correct sequence of events.

restore full1.bak with no recovery
restore dif3.bak  with no recovery
restore trans6.trn  with no recovery
restore trans7.trn  with recovery

Could someone confirm the logic and write the sql statements.
aslo, if tran7.trn is correct with recovery.... how do i then go about choosing the point in time
0
Comment
Question by:jamesmetcalf74
  • 3
5 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 40549045
yes, that is all the files you need.
the SQL is described in the official documentation:
http://msdn.microsoft.com/en-us/library/ms179451.aspx
RESTORE DATABASE AdventureWorks
   FROM AdventureWorksBackups
   WITH FILE=3, NORECOVERY;

RESTORE LOG AdventureWorks
   FROM AdventureWorksBackups
   WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

RESTORE LOG AdventureWorks
   FROM AdventureWorksBackups
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE DATABASE AdventureWorks WITH RECOVERY; 
GO

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 40549046
except that in your case, you will not say "FROM DATABASE" but FROM DEVICE, fully explained here:
http://msdn.microsoft.com/en-us/library/ms186858.aspx
0
 

Author Comment

by:jamesmetcalf74
ID: 40549143
Thanks Guy
Quick Question.
the time stamp on the backup files....   that signifies what exactly.
for instance if a bak file has the attribute

jan 16 1800

does that mean that is the frozen database state at jan 16 1800 or
when the file creation was started or
when the file creation completed.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 40549289
I am not sure, and it does actually not matter.
to know from when the backup file really is use
RESTORE HEADERONLY
FROM <backup_device>

that will inform you from when the backup was.
the timestamps there are the start of the backups, as that one is relevant for the contents.
data changes (update/insert/delete) after the backup start is NOT backed up
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 40549305
I prefer to use NORECOVERY on all restores, then do one final recovery after all logs have been applied:

RESTORE DATABASE Johnson1
FROM DISK = 'full1.bak'
WITH NORECOVERY

RESTORE DATABASE Johnson1
FROM DISK = 'dif3.bak'
WITH NORECOVERY

RESTORE LOG Johnson1
FROM DISK = 'trans6.trn'
WITH NORECOVERY, STOPAT = '20150116 18:00'

RESTORE LOG Johnson1
FROM DISK = 'trans7.trn'
WITH NORECOVERY, STOPAT = '20150116 18:00'

RESTORE DATABASE Johnson1 WITH RECOVERY
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
backups - Strategies 1 21
Help with simplifying SQL 6 48
Get Duration of last Status Update 4 29
Where to download and how to install sqldmo.dll 5 33
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now