Help with Restore Scenario MSSQL

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
jamesmetcalf74Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
jamesmetcalf74Author Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.