Solved

Help with Restore Scenario MSSQL

Posted on 2015-01-14
5
110 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 143

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 143

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 143

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:Scott Pletcher
Scott Pletcher 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

621 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