<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by James DiMauro
»
Moving Databases with the ALTER Command
20 posts, Page 2 of 2
««
1
2
Moving Databases with the ALTER Command
Rate Topic
Display Mode
Topic Options
Author
Message
imSQrLy
imSQrLy
Posted Wednesday, August 26, 2009 12:51 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 10:17 AM
Points: 133,
Visits: 437
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
K. Brian Kelley
K. Brian Kelley
Posted Wednesday, August 26, 2009 1:36 PM
Keeper of the Duck
Group: Moderators
Last Login: Yesterday @ 1:55 PM
Points: 6,584,
Visits: 1,789
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
george sibbald
george sibbald
Posted Wednesday, August 26, 2009 4:01 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 5,269,
Visits: 11,207
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
Ray Herring
Ray Herring
Posted Wednesday, August 26, 2009 4:35 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:17 AM
Points: 334,
Visits: 280
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
el-slo
el-slo
Posted Wednesday, August 26, 2009 8:01 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:01 AM
Points: 7,
Visits: 26
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
John Mitchell-245523
John Mitchell-245523
Posted Thursday, August 27, 2009 2:35 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 4,428,
Visits: 7,207
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
the sqlist
the sqlist
Posted Thursday, August 27, 2009 10:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 124,
Visits: 471
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
imSQrLy
imSQrLy
Posted Thursday, August 27, 2009 11:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 10:17 AM
Points: 133,
Visits: 437
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
thehumansponge
thehumansponge
Posted Thursday, August 27, 2009 11:39 AM
Forum 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
computercio
computercio
Posted Friday, September 04, 2009 12:52 PM
Forum 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 »
20 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
