Post reply

Restore 2005 Database Error

  • June 4, 2008 at 4:01 pm

    #186516

    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

    #824585

    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

    #824718

    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

    #824960

    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

    #824969

    Are you using WITH REPLACE?

  • June 5, 2008 at 11:02 am

    #824973

    No, should I use that?

    Thanks

    Andrew SQLDBA

  • June 5, 2008 at 1:57 pm

    #825090

    Yes. REPLACE tells SQL to overwrite an existing database.

    😎

  • June 5, 2008 at 2:35 pm

    #825115

    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