Moving Databases with the ALTER Command

Question

Moving Databases with the ALTER Command

imSQrLy

Ten Centuries

Points: 1335
More actions
August 26, 2009 at 12:18 am

#209920

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!"

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Private Gripweed Right there with Babe Points: 772 More actions · Answer 1

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 + '")'

Johan van Tonder Old Hand Points: 371 More actions · Answer 2

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 🙂

Private Gripweed Right there with Babe Points: 772 More actions · Answer 3

Private Gripweed

Right there with Babe

Points: 772

August 26, 2009 at 11:27 am

#1045030

LOL that is funny, I did not think about it that way.

rondad SSC-Addicted Points: 423 More actions · Answer 4

Has anyone tested the script with using a unc path for the new target location rather than a "hard-coded" drive letter?

Ron

Private Gripweed Right there with Babe Points: 772 More actions · Answer 5

Private Gripweed

Right there with Babe

Points: 772

August 26, 2009 at 11:54 am

#1045055

Easily tested. But I have not

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 6

EDIT: Brian needs to read more closely. 🙂

K. Brian Kelley
@kbriankelley

imSQrLy Ten Centuries Points: 1335 More actions · Answer 7

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!"

imSQrLy Ten Centuries Points: 1335 More actions · Answer 8

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!"

imSQrLy Ten Centuries Points: 1335 More actions · Answer 9

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!"

imSQrLy Ten Centuries Points: 1335 More actions · Answer 10

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 SSC Guru Points: 114562 More actions · Answer 11

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 SSC Guru Points: 104210 More actions · Answer 12

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 SSCertifiable Points: 5533 More actions · Answer 13

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 SSC Rookie Points: 35 More actions · Answer 14

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!