9

Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up.

According to MSDN I should indeed be backing up my tail log before restoring:

If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.

What are some dangers or disadvantages of the way I'm doing it? Why is backing up the tail log first of advantage to me?

I'm using SQL Server 2008R2 but I'm presuming this query will be relevant to most newer versions of SQL Server also so have not tagged it as such initially.

  • 6
    I think that means if you're going to restore in the same location (and potentially apply additional transaction logs). If you're going to be restoring just a simple copy of the database elsewhere, and don't need to maintain the log chain, I'd use the method you're using. I might even use WITH COPY_ONLY on the backup. – Aaron Bertrand Aug 22 '14 at 13:37
4

If you don't back up the tail of the log you lose any transactions that ocurred since you last backed up the database.

  • 1
    Yes I understood that. But even so I think your answer has made me bridge the gap in my thinking. See the non-production DB is never backed up, so I'm losing the entire DB by restoring anyway so why would I care about the tail log. But the MSDN thinking is that I do always back up my DB, the only bit not backed up right now is the tail log hence they want me to back it up. To my specific scenario of a transient non-production DB that no one cares about therefore there is no advantage to backing up the tail log. – Paul Aug 22 '14 at 13:41
  • 2
    Completely right. If you just want to bring data to a non-production environment and don't really care for what was in there, then there is really no problem at all. – JoseTeixeira Aug 22 '14 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.