June 4, 2008 at 4:01 pm
Hello Everyone
I am in the process of creating a backup and restore process that will take my prod database, perform a full backup that is placed on a drive on the Dev Database server, and then restoring that backup to the Dev Server. I have created the Full backup using a simple maintenance task. I first shrink the transaction log and then perform the full backup. This produces one backup file. Pretty simple stuff so far.
I am getting an error when I try to restore the backup file over top of the Development database of the same name.
The tail of the log for the database " " 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.
I have been manually doing this process when needed, say once per month or so, and it would restore fine when using SSMS. But now I am using all commands in a query window.
I have not found much online that describes this error. Can anyone give me a clue as to how I can take a full backup from one box, and then restore that file on a different box.
Thanks in advance
Andrew SQLDBA
June 4, 2008 at 4:20 pm
Open transaction maybe? Must be some setting on the dev box to prevent this.
This help: http://technet.microsoft.com/en-us/library/bb283410.aspx
Are you using WITH REPLACE?
June 5, 2008 at 5:50 am
It's not necessary to shrink the logfile before doing a full backup (if it's your intention to compact the size of your backup). Although a full backup contains all the information about data and logfiles, it only contains filled datapages.
Wilfred
The best things in life are the simple things
June 5, 2008 at 10:44 am
Thanks for the advice
So why is SQL throwing this error? I need to be able to take a backup and restore the database on a different server using the commands, and not the GUI.
I have even tried the code that SQL produces in the maintenance task. It will preform the backup just fine, but I cannot restore that backup to another server with the same database name.
What is the restore syntax that I can use to make this happen?
Thanks
Andrew SQLDBA
June 5, 2008 at 11:00 am
Are you using WITH REPLACE?
June 5, 2008 at 11:02 am
No, should I use that?
Thanks
Andrew SQLDBA
June 5, 2008 at 1:57 pm
Yes. REPLACE tells SQL to overwrite an existing database.
😎
June 5, 2008 at 2:35 pm
Thank You
That was silly of me to ask that. The SQL Books online explained that. I read that yesterday after being asked if I use it, but was not able to try it until today.
That did it, everything works fine now.
Thanks to everyone for their comments and suggestions
Andrew SQLDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply