question

vivekchandra09 avatar image
vivekchandra09 asked

Different kind of Log Shipping

So we are using Veeam to backup the entire Virtual machine. That backup is being planned to be restored to another box and from there the full backup and the transaction log backup will be copied to a test box. I have installed SQL server 2008 on this test server. Will I be able to restore the SQL Server database on this test server or do I need to change something?
sql-server-2008log-shipping
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Tim avatar image
Tim answered
I am not 100% clear on what you are asking. So Veeam is going to backup and restore the Virtual machine. Clear on that. On that virtual machine you have the database backup and logs. You will then copy those backups and logs to another test machine you have that you installed SQL 2008 on and want to know if you can restore those backups and logs. Is this what you are asking? If so then yes, but I am not clear why you would need to restore the Veeam backup to copy the backup files. Just copy from the current online virtual machine to your test server and validate you can restore the database backup file and log files. As long as you are restoring to the same version of SQL or newer and have valid backup files you can restore. If I misunderstood your question please let me know.
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vivekchandra09 avatar image vivekchandra09 commented ·
So this is what I did. I copied the full backup from the current production server to another server. I was able to restore from the backup. However, when I try to apply transaction log backup, I get an error" The log or differential backup cannot be restored because no files are ready to rollforward (Microsoft.SqlServer.smo).
0 Likes 0 ·
Tim avatar image Tim commented ·
When you restore the full you need to restore with no_recovery so that you can apply addition backups such as differential and tlogs.
0 Likes 0 ·
vivekchandra09 avatar image vivekchandra09 commented ·
thanks it works. Not sure about nounload. May be this is for a offsite backup every Thursday.
0 Likes 0 ·
Tim avatar image Tim commented ·
Awesome, really glad to have been able to help out and even more glad you now know how to apply multiple backups. This would have sucked if you had to perform this in production and have to figure all this out for the first time. You are prepared now. :)
0 Likes 0 ·
vivekchandra09 avatar image vivekchandra09 commented ·
so the problem reappears for another set of log backups. so I can restore 12:30 AM, 1:00 AM and 1:30 AM transaction logs. The moment I try to restore 2 AM transaction logs, I get this error: RESTORE LOG [CVS] FROM DISK = N'D:\SQLfiles\CVSbackup_2015_03_19_020001_4020696.trn' WITH FILE = 1, NOUNLOAD, STATS = 10, norecovery GO Msg 4305, Level 16, State 1, Line 1 The log in this backup set begins at LSN 255069000000156000001, which is too recent to apply to the database. An earlier log backup that includes LSN 255068000000721900001 can be restored. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.
0 Likes 0 ·
Show more comments
vivekchandra09 avatar image
vivekchandra09 answered
RESTORE DATABASE [CVS] FROM DISK = N'D:\CVS_backup_2015_03_13_000001_2723238.bak' WITH FILE = 1, MOVE N'CVSNET_Data' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CVS.mdf', MOVE N'CVSNET_Entity' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CVS_1.ndf', MOVE N'CVSNET_Relational' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CVS_2 .ndf', MOVE N'CVSNET_Reference' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ CVS_3.ndf', MOVE N'CVSNET_Note' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ CVS_4.ndf', MOVE N'CVSNET_Transaction' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ MSSQL\DATA\CVS_5.ndf', MOVE N'CVSNET_System' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER \MSSQL\DATA\CVS_6.ndf', MOVE N'CVSNET_Audit' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER \MSSQL\DATA\CVS_7.ndf', MOVE N'CVSNET_Log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\CVS_8.ldf', NOUNLOAD, STATS = 10 GO ----------------- This is the scripted restore file. Where do I put no_recovery option in this script to further allow application of difflog and tlog?
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
after stats = 10. Just add a comma and NO_RECOVERY. NOUNLOAD, STATS = 10, NO_RECOVERY GO Are you using a tape library? I haven't seen NOUNLOAD in a decade.
0 Likes 0 ·
vivekchandra09 avatar image
vivekchandra09 answered
I am able to restore the full backup and 2 tlogs. But any attempt to apply other tlog backup gives me following error: RESTORE LOG [CVS] FROM DISK = N'D:\SQLfiles\CVS_backup_2015_03_14_020001_5024109.trn' WITH FILE = 1, NOUNLOAD, STATS = 10,norecovery GO Msg 4305, Level 16, State 1, Line 1 The log in this backup set begins at LSN 254230000000371700001, which is too recent to apply to the database. An earlier log backup that includes LSN 253906000004847800001 can be restored. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
This message indicates you are missing a tlog. If you applied two logs but this last one is failing saying the LSN is to recent, then there must be a log or logs between the last successful log you applied and the one you are trying to apply now.
0 Likes 0 ·
vivekchandra09 avatar image vivekchandra09 commented ·
I was able to fix this myself. Please ignore my previous question.
0 Likes 0 ·
Tim avatar image Tim commented ·
cool, just restoring the logs out of order?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.