Be Careful! SQL Server Backups Can Bite You!

Here is a riddle for you . . .

You're requested to move a database from an old server to a new server. As a professional and experienced DBA, you prepare a checklist of actions to take, you script everything, you document your plan, and, of course, you test it before doing it for real. Without getting into all the details, the plan includes the following steps:

After you prepare everything in advance, you first perform the test. Unfortunately, you don't have a test environment, so you perform the test in the production environment, but without all the steps involving the application. You move all the server-level objects (such as logins and linked servers) to the new server. You back up the database, copy the backup file to the new server and restore it there. Then you run some tests on the restored database, and everything seems to be just fine. You're happy with the results, and you go home confident in your plan, ready for the actual production migration planned for the day after.

Related: A Safe Method for Moving a Database to a New Location

After a good sleep, you go to the office early in the morning and start the real migration. You perform all the steps successfully, and you manage to complete all of them in less than two hours without any errors. Now it's time for coffee, while the product team tests the application and verifies that everything is in place.

And then comes a surprise . . . the product team tells you that data is missing. It seems that all the changes made since yesterday are gone. People become nervous, and everyone is looking at you, waiting for answers. Your happy face is replaced with something like this:

What happened here? How can it be?

Want to know what happened?  Read the whole story here.