Left overs

A Hungry Squirrel stealing bird food.

If you are migrating a database from one database to another, you want to make a full backup right before shutting the database down.

Here are some things we don't want.

1. We don't any users modifying the database once we start the process.  Imagine a user adding a record as you back up the database.  The database won't contain that record, and now it's lost.  That's bad.
2. We don't want to throw away the database on the source server until we are sure the destination is up and working. 

What we do is 

• Set the database to single_user mode.  This prevents anyone from modifying anything.
• Take a full backup of the database
• Restore the backup to the new server
• fix the orphaned users.

I did this recently and left the source database in Single_user Mode.  This caused the backups to fail.  

This script fixes the problem. Note:  I always do this remotely logged into the database server.

use olddb
Alter Database olddb set single_user with rollback immediate;
backup olddb to disk ='d:\olddb.bak' with copy_only
alter database olddb modify name =olddb_Migrated;
alter database olddb_migrated set multi_user;
use master;
alter database olddb_Migrated set offline;