<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 1 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:18 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 28, 2013 11:42 AM
Points: 133,
Visits: 438
Comments posted to this topic are about the item
Moving Databases with the ALTER Command
Jimmy
"I'm still learning the things i thought i knew!"
Post #777288
Private Gripweed
Private Gripweed
Posted Wednesday, August 26, 2009 10:12 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, October 09, 2012 10:36 AM
Points: 30,
Visits: 130
The script works well with one exception. If your logical name for the database files contains spaces, it will cause issues.
If you alter the stored procedure and enclose the logical name for the database file in double quotes, this fixes the issue. See below:
set @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = "' + @LogicalName + '", FILENAME = "' + @NewPath + '")'
Post #777649
Johan van Tonder
Johan van Tonder
Posted Wednesday, August 26, 2009 11:13 AM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, February 04, 2013 12:39 AM
Points: 95,
Visits: 347
Very good method.
I'm just not sure if I would want to be any one of the users connected to the DB while the script is run, since I will get killed
Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Yes, I know what's being meant, but that gave me laugh though
Post #777687
Private Gripweed
Private Gripweed
Posted Wednesday, August 26, 2009 11:27 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, October 09, 2012 10:36 AM
Points: 30,
Visits: 130
LOL that is funny, I did not think about it that way.
Post #777691
rondad
rondad
Posted Wednesday, August 26, 2009 11:41 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, April 25, 2013 4:51 PM
Points: 13,
Visits: 24
Has anyone tested the script with using a unc path for the new target location rather than a "hard-coded" drive letter?
Ron
Post #777697
Private Gripweed
Private Gripweed
Posted Wednesday, August 26, 2009 11:54 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, October 09, 2012 10:36 AM
Points: 30,
Visits: 130
Easily tested. But I have not
Post #777718
K. Brian Kelley
K. Brian Kelley
Posted Wednesday, August 26, 2009 12:11 PM
Keeper of the Duck
Group: Moderators
Last Login: Today @ 7:47 AM
Points: 6,584,
Visits: 1,796
EDIT: Brian needs to read more closely. :)
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 #777734
imSQrLy
imSQrLy
Posted Wednesday, August 26, 2009 12:47 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 28, 2013 11:42 AM
Points: 133,
Visits: 438
Johan van Tonder (8/26/2009)
Very good method.
I'm just not sure if I would want to be any one of the users connected to the DB while the script is run, since I will get killed
Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Yes, I know what's being meant, but that gave me laugh though
Bang your dead!
I haven't laughed like this all week.
Jimmy
"I'm still learning the things i thought i knew!"
Post #777764
imSQrLy
imSQrLy
Posted Wednesday, August 26, 2009 12:48 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 28, 2013 11:42 AM
Points: 133,
Visits: 438
Private Gripweed (8/26/2009)
The script works well with one exception. If your logical name for the database files contains spaces, it will cause issues.
If you alter the stored procedure and enclose the logical name for the database file in double quotes, this fixes the issue. See below:
set @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = "' + @LogicalName + '", FILENAME = "' + @NewPath + '")'
Thanks for pointing that out. I will update my script.
Jimmy
"I'm still learning the things i thought i knew!"
Post #777765
imSQrLy
imSQrLy
Posted Wednesday, August 26, 2009 12:49 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 28, 2013 11:42 AM
Points: 133,
Visits: 438
rondad (8/26/2009)
Has anyone tested the script with using a unc path for the new target location rather than a "hard-coded" drive letter?
Ron
I have not tested it like that.
Jimmy
"I'm still learning the things i thought i knew!"
Post #777766
« Prev Topic
|
Next Topic »
20 posts, Page 1 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.
