Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Moving Databases with the ALTER Command Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 12:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
K. Brian Kelley (8/26/2009)
You may want to add a warning that your script is going to turn on xp_cmdshell in order to perform the move. This may balk someone's configuration, especially if policy management is on.


At the bottom of the article it states:
Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Anyone connected will be kicked off. The script does set the db back to multi user when it brings the database back online after the move.This script also turns on and uses the xp_cmdshell advanced option. It will set this option back to its original setting (enabled or disabled) at the end of the script.


Jimmy

"I'm still learning the things i thought i knew!"
Post #777768
Posted Wednesday, August 26, 2009 1:36 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
imSQrLy (8/26/2009)
At the bottom of the article it states:
Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE. Anyone connected will be kicked off. The script does set the db back to multi user when it brings the database back online after the move.This script also turns on and uses the xp_cmdshell advanced option. It will set this option back to its original setting (enabled or disabled) at the end of the script.


Yup, I see that now on a second read-through. I apologize.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #777810
Posted Wednesday, August 26, 2009 4:01 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
just wanted to point out that if using the backup\restore method to move the database files you do not need to delete\rename the database, just use the 'with move' clause in the restore.

---------------------------------------------------------------------

Post #777927
Posted Wednesday, August 26, 2009 4:35 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 464, Visits: 371
You should add a check for AutoStats. If you set the database to single user and AutoStats gets started you may have problems regaining access. I used a similar approach to merge two databases into one (at 100+ customer sites) and I did run into the problem.

Also, IMHO if you do this often enough to justify writing a stored procedure then you really should have a lot more error checking built into the code.
Post #777953
Posted Wednesday, August 26, 2009 8:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 10:01 AM
Points: 7, Visits: 41
Works well in SQL Server 2005, but doesn't in 2000 which returns the error:

EXECUTE cannot be used as a source when inserting into a table variable.


Apparently 2000 doesn't allow the output of a stored procedure to be inserted to a table variable. Would be good to have mentioned this in the article, and would be even better if a solution was suggested!
Post #777999
Posted Thursday, August 27, 2009 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 5,339, Visits: 9,794
Thanks for the article, Jimmy. One thing you might want to consider is that if you have large data files with small amounts of data in them, backup and restore may be the quicker option. This is because backup and restore will only move the data, whereas detach-move-attach will move the whole data files, including empty space. I know that the restore process will also need to create the data file in the new location, but in a test I did on a single disk PC, it took more than 100 times longer to copy a 10GB database file into a new location than it did to create the file in the first place.

John
Post #778103
Posted Thursday, August 27, 2009 10:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:44 AM
Points: 136, Visits: 611
What hapens to the index files (NDF) if they exist?

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #778444
Posted Thursday, August 27, 2009 11:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
the sqlist (8/27/2009)
What hapens to the index files (NDF) if they exist?


The primary and secondary data files are moved as well, to the same location. If you need them to go on different disks you would need to modify the script.


Jimmy

"I'm still learning the things i thought i knew!"
Post #778497
Posted Thursday, August 27, 2009 11:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 23, 2009 7:34 AM
Points: 1, Visits: 23
Found an issue with this script, if a Database is off-line ... it will be brought back online without moving the mdf and ldf files.
Post #778542
Posted Friday, September 4, 2009 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 10, 2009 9:04 AM
Points: 1, Visits: 7
Does this move indexes too?
Post #783156
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse