SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Best user mode prior to backing up database.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Toothpick
Starting Member

United Kingdom
4 Posts

Posted - 11/26/2013 :  09:52:24  Show Profile  Reply with Quote
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 immediate

I plan to use these backups to restore onto the new server.

Thanks.

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 11/26/2013 :  12:35:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do these users have dbo? If not, then this will do it: alter database <db name> set restricted_user with rollback immediate

Additionally I disable the logins when performing a migration.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 11/26/2013 :  13:08:59  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 11/26/2013 :  13:25:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
+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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Toothpick
Starting Member

United Kingdom
4 Posts

Posted - 11/27/2013 :  05:38:03  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000