June 7, 2011 at 7:07 am
I'm taking full and transaction log backups, copying the files to a test server and trying to restore them. I'm running into a slew of issues here. Here are my steps:
BACKUP DATABASE MyDB
To DISK = 'G:\SQLbkp\MyDB_Full_20110607.bak'
GO
BACKUP LOG MyDB
TO DISK = 'G:\SQLbkp\MyDB_LOG_20110607.TRN'
After copying the files over I try to restore on the test server:
Alter Database MyDB
SET SINGLE_USER With ROLLBACK IMMEDIATE
GO
RESTORE DATABASE MyDB
FROM DISK = 'E:\From Prod\MyDB_Full_20110607.bak'
WITH NORECOVERY
go
RESTORE LOG MyDB
FROM DISK = 'E:\From Prod\MyDB_LOG_20110607.TRN'
WITH RECOVERY
However, I'm receiving the following error messages
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "MyDB" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Any help you can provide would be greatly appreciated
June 7, 2011 at 7:10 am
So you're trying to backup [myDB] on server A and restore [myDB] to server B where there already exists a [myDB].
As the error states, you have to use the WITH REPLACE option to overwrite the existing database. This will overwrite the [myDB] on server B with the backup from server A.
RESTORE DATABASE MyDB
FROM DISK = 'E:\From Prod\MyDB_Full_20110607.bak'
WITH NORECOVERY, REPLACE
go
RESTORE LOG MyDB
FROM DISK = 'E:\From Prod\MyDB_LOG_20110607.TRN'
WITH RECOVERY
edit: typos
June 7, 2011 at 7:13 am
Thank you so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply