sqlfriends (11/8/2012)
We would like to migrate databases from one server to another production server.Before I do a backup on the old server, we would like no one connect to the server and do any transactions other than me the dba. So I decide to use set the databases to single-user mode only, then do backup of all the databases. Then use the backup files to restore to another server.
My question, do I need to pay attention to any of this process to insure the backup and restore is correct?
Thanks,
What transactions do you want to be able to run after you set the database to single user mode? Normally, I would set the database to read only, take a backup of it, restore the database on the 2nd server, and then take that database out of read only mode. That guarantees no writes occur to the database, but still allows you to read from it.
If you want to make sure that no one connects to the SQL Server instance, you can open the connections that you need in SSMS, pause the SQL Server Service and then terminate any other open connections to the database. Of course, you will need to make sure that no one else re-starts the paused service or other connections will be allowed again.