Solved

Help with Restore Scenario MSSQL

Posted on 2015-01-14
5
101 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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