Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with Restore Scenario MSSQL

Posted on 2015-01-14
5
Medium Priority
?
117 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 1500 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 1500 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 1500 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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 …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 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