Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Toothpick
Starting Member
4 Posts |
 Posted - 2013-11-26 : 09:52:24
|
| Hi,I have to move 6 databases running under 2005 sp2 to a new server.All of the db's are heavily used,and so there will be open connections when the time comes for me to do the backups prior to moving the db's.Even though the users will know well in advance that down time is required, i am expecting open transactions to be present. I don't want any open/active sessions in the db's during the FULL backups, so what is the best way to do this?Put all the db's in single user mode prior to the backups using a command like:alter database <db name> set single_user with rollback immediateI plan to use these backups to restore onto the new server.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-26 : 12:35:53
|
| Do these users have dbo? If not, then this will do it: alter database <db name> set restricted_user with rollback immediateAdditionally I disable the logins when performing a migration.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-11-26 : 13:08:59
|
| Have you considered using database mirroring to migrate your database to the new server? You can back up and restore at your leisure and allow full use until you're ready to move, then do a failover. At worst you'd only lose in-flight transactions during failover, and you can do 1 database at a time while the others continue operating. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-26 : 13:25:10
|
| +1 for the mirroring solution. That's almost always what I use. We do it gracefully by shutting down the web applications and ensuring all connections are out. Then I disable the logins on the source server to ensure no transactions slip through. Do the failover, switcheroo the connection string or rename the server and bam back in business.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Toothpick
Starting Member
4 Posts |
Posted - 2013-11-27 : 05:38:03
|
| Thanks for the advice.We'll be going down the straight backup and restore route on this one. I've checked the logins, and no one has dbo rights, so i'll go with the restricet_user with rollback immediate option.AtB,Shane |
|
|
|
|
|
|
Best user mode prior to backing up database.