Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Newbie - Applying Tran log

Status
Not open for further replies.

R7Dave

Programmer
Oct 31, 2007
181
US
Hello - I am a newbie and trying to apply multiple transaction logs to a database.

Below is my script - I wrote it weeks ago and do not understand how I can use 'NJnew' (this is the logical name?) in the command "MOVE N'NJnew' TO "

Code:
RESTORE DATABASE [NJCCCS$DOE_Dev3] FROM  DISK = N'J:\DCF_TEST\TEST_DEEGAN_BACKUP\testbackupfilename' 
WITH  RECOVERY,  
MOVE N'NJnew' TO N'E:\MSSQL10.MSSQLSERVER\MSSQL\Data\NJCCCS$DOE_Dev3.mdf',  
MOVE N'NJnew_log' TO N'G:\MSSQL10.MSSQLSERVER\MSSQL\TransLog\NJCCCS$DOE_Dev3.ldf',  
NOUNLOAD,  STATS = 10

Here is the whole thing....

First back up the database, full recovery

Code:
BACKUP DATABASE [NJCCCS$DOE_Dev3] 
TO  DISK = N'J:\DCF_TEST\TEST_DEEGAN_BACKUP\testbackupfilename' 
WITH NOFORMAT, NOINIT,  
NAME = N'NJCCCS$DOE_Dev3-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

then apply transaction logs...

Code:
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018040002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018041502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018043001.trn'  WITH NORECOVERY

then restore the database ...

Code:
RESTORE DATABASE [NJCCCS$DOE_Dev3] FROM  DISK = N'J:\DCF_TEST\TEST_DEEGAN_BACKUP\testbackupfilename' 
WITH  RECOVERY,  
MOVE N'NJnew' TO N'E:\MSSQL10.MSSQLSERVER\MSSQL\Data\NJCCCS$DOE_Dev3.mdf',  
MOVE N'NJnew_log' TO N'G:\MSSQL10.MSSQLSERVER\MSSQL\TransLog\NJCCCS$DOE_Dev3.ldf',  
NOUNLOAD,  STATS = 10

The script makes sense to me except for the MOVE N'NJnew' part - I don't understand where the string 'NJnew' is being assigned to be used with the MOVE command

Also, will this work? I'm not able to test the script and a coworker believes I must

1- Back up the existing database
2- Drop the existing database
3- Restore the database
4- Apply tranlog
5- Open database

Thanks in advance for any input
Dave
 
Your scripts are in the wrong order.

You backup the database and logs. When it is time to restore you use the RESTORE DATABASE, then the RESTORE LOG statemnets.

The MOVE parameter tells the SQL Server to put the physical file in a different location than it was when it was backed up. The MOVE parameter is optional.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog
 
Thanks Denny - I corrected the script (below)

What you say makes more sense, but I grabbed the script from
Code:
RESTORE DATABASE AdventureWorks2008R2
   FROM AdventureWorks2008R2_1
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 1,
   NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 2,
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 3,
   WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks2008R2
   WITH RECOVERY
GO

Here is mine corrected like you said, with the transaction log applied with recovery as the last step

Code:
BACKUP DATABASE [NJCCCS$DOE_Dev3] 
TO  DISK = N'J:\DCF_TEST\TEST_DEEGAN_BACKUP\testbackupfilename' 
WITH NOFORMAT, NOINIT,  
NAME = N'NJCCCS$DOE_Dev3-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [NJCCCS$DOE_Dev3] FROM  DISK = N'J:\DCF_TEST\TEST_DEEGAN_BACKUP\testbackupfilename' 
WITH  NORECOVERY

RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018040002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018041502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018043001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018044501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018050001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018051501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018053001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018054501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018060003.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018061502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018063003.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018064502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018070002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018071502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018073002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018074501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018080002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018081501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018083001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018084502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018090002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018091501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018093002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018094502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018100001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018101501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018103001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018104501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018110001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018111501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018113002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018114501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018120002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018121501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018123001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018124502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018130002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018131501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018133001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018134502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018140002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018141502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018143001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018144502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018150002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018151501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018153002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018154501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018160002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018161502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018163001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018164501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018170002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018171501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018173001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018174502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018180001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018181501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018183001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018184501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018190001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018191502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018193001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018194502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018200003.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018201502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018203001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018204502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018210002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018211502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018213002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018214501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018220001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018221505.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018223001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018224502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018230001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018231501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018233001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101018234501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019000001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019001506.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019003002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019004502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019010001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019011502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019013001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019014502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019020002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019021501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019023001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019024502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019030002.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019031501.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019033001.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019034502.trn'  WITH NORECOVERY
RESTORE LOG [NJCCCS$DOE_Dev3] FROM DISK = 'J:\DCF_TEST\NJNEW_20101019040002.trn'  WITH RECOVERY

I'm not able to test this yet so if you can tell me if what I have looks correct for applying multiple transaction logs - I would appreciate it.

Thanks
Dave
 
Yep, that looks correct. You don't actually need to do the backup at the beginning of the script. That is a safety measure in the sample scripts. If you don't want to backup the existing database (and you know that it is safe to delete) then add ", REPLACE" to the RESTORE DATABASE statement.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog
 
Nope, you have a major issue with your script. If you run your backup script and then the restore, all you are doing is restoring what you just backed up. If you are making a backup of the existing database so you can restore it if anything goes wrong, then you need to make the backup using a different filename - such as testbackupfilename_20110113.bak. And that is the other thing, I don't see you giving your backup filename an extension (.bak).

If you are backing it up to then restore it somewhere else, the transaction logs won't restore since they don't come after the last full backup.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
Thank you for your response - now I am receiving the error

The backup set holds a backup of a database other than the existing 'NJCCCS$DOE_Dev3' database.

I believe it is because I made a copy of another database - I'm still working on it - if anyone has suggestions, they would be appreciated.

Thanks
Dave
 
Check your log backup sequence. Make sure you aren't restoring a log backup that was created before the full backup.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
Adding the WITH REPLACE option (or ", REPLACE" to your existing RESTORE DATABASE script) will get rid of that error message.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top