Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Databases with the ALTER Command


Moving Databases with the ALTER Command

Author
Message
imSQrLy
imSQrLy
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 473
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!"
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (7.5K reputation)

Group: Moderators
Points: 7542 Visits: 1917
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
@‌kbriankelley
george sibbald
george sibbald
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7300 Visits: 13687
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.

---------------------------------------------------------------------
Ray Herring
Ray Herring
SSC Eights!
SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)

Group: General Forum Members
Points: 862 Visits: 571
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.
el-slo
el-slo
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 42
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!
John Mitchell-245523
John Mitchell-245523
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9392 Visits: 15541
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
the sqlist
the sqlist
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 724
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
imSQrLy
imSQrLy
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 473
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!"
thehumansponge
thehumansponge
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
computercio
computercio
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
Does this move indexes too?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search