MS SQL full restore + transactional log restore

Dear all,

as we do daily SQL 2005 full + transaction log backup, we should do a full restore but leave the DB in non recovery model, then followed by transaction log restore, then change the DB to ready state?

what is the command for that?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
exactly. as long as you recover the transactions logs WITH NORECOVERY, you can continue to do so. the only restriction is to restore them in the correct order (sql server will watch and enforce that).
once you restore WITH RECOVERY (explicitely or implicitly by leaving that part of the sql away), you cannot restore any logs to that database, unless you restart a full database restore with norecovery.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the first restore is a
RESTORE DATABASE <dbname> FROM <backup_device_specs> WITH NORECOVERY;

next, you can indeed apply log files:
RESTORE LOG <database_name> FROM <backup_device_secps> WITH NORECOVERY;

until you decide to go "online":
RESTORE DATABASE <database_name> WITH RECOVERY;

that's it
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so can't combine RESTORE LOG <database_name> FROM <backup_device_secps> WITH NORECOVERY;  and RESTORE DATABASE <database_name> WITH RECOVERY; together ?

for example, full DB restore:

RESTORE DATABASE [yyy] FROM  DISK = N'E:\Data\MSSQL\backup\yyy_bak' WITH  FILE = 1,  
MOVE N'BTN905_db' TO N'E:\Data\MSSQL\Data\yyy.mdf',  MOVE N'yyy_log' 
TO N'E:\Data\MSSQL\Data\yyy_1.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Open in new window


and it is in no recovery state. then followed  by the transcation log backup together with online option ?

RESTORE LOG [yyy] FROM  DISK = N'E:\Data\MSSQL\backup\yyy.log' WITH  
FILE = 1,  NOUNLOAD,  STATS = 10
GO

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure you can, for the last RESTORE LOG you can do WITH RECOVERY:

RESTORE LOG <database_name> FROM <backup_device_secps> WITH RECOVERY;

for the full technical reference:
http://technet.microsoft.com/en-us/library/ms177446.aspx
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"sure you can, for the last RESTORE LOG you can do WITH RECOVERY:"

basically I generate that one from SSMS, so I think if I don't do this WITH RECOVERY, it will assume that automatically ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the link I gave above has another link:
http://technet.microsoft.com/en-us/library/ms186858.aspx

which clarifies that one, and indeed it's: RECOVERY (the default)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so my script is correct I think ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
but should I said I have to restore all Transcation log one by one in order and for any transcation log in between the full restore and the last trans log restore, the transaction log restore can only do non recovery mode?

RESTORE LOG [yyy] FROM  DISK = N'E:\Data\MSSQL\backup\yyy.log' WITH  
FILE = 1,  NORECOVERY, NOUNLOAD,  STATS = 10
GO 

Open in new window


As only the last transcation log restore can remove the NORECOVERY ?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"sql server will watch and enforce that "

yes, it will prompt for an error if the log is not in order, right?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, exactly
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.