SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by James DiMauro / Moving Databases with the ALTER Command / Latest Posts

RE: Moving Databases with the ALTER Command

computercio — Fri, 04 Sep 2009 12:52:46 GMT

Does this move indexes too?

RE: Moving Databases with the ALTER Command

thehumansponge — Thu, 27 Aug 2009 11:39:43 GMT

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.

RE: Moving Databases with the ALTER Command

imSQrLy — Thu, 27 Aug 2009 11:07:30 GMT

[quote][b]the sqlist (8/27/2009)[/b][hr]What hapens to the index files (NDF) if they exist?[/quote]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.

RE: Moving Databases with the ALTER Command

the sqlist — Thu, 27 Aug 2009 10:07:53 GMT

What hapens to the index files (NDF) if they exist?

RE: Moving Databases with the ALTER Command

John Mitchell-245523 — Thu, 27 Aug 2009 02:35:33 GMT

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

RE: Moving Databases with the ALTER Command

el-slo — Wed, 26 Aug 2009 20:01:27 GMT

Works well in SQL Server 2005, but doesn't in 2000 which returns the error:[quote]EXECUTE cannot be used as a source when inserting into a table variable.[/quote]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!

RE: Moving Databases with the ALTER Command

Ray Herring — Wed, 26 Aug 2009 16:35:22 GMT

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.

RE: Moving Databases with the ALTER Command

george sibbald — Wed, 26 Aug 2009 16:01:54 GMT

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.

RE: Moving Databases with the ALTER Command

K. Brian Kelley — Wed, 26 Aug 2009 13:36:52 GMT

[quote][b]imSQrLy (8/26/2009)[/b][hr]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.[/quote]Yup, I see that now on a second read-through. I apologize.

RE: Moving Databases with the ALTER Command

imSQrLy — Wed, 26 Aug 2009 12:51:31 GMT

[quote][b]K. Brian Kelley (8/26/2009)[/b][hr]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.[/quote]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.

RE: Moving Databases with the ALTER Command

imSQrLy — Wed, 26 Aug 2009 12:49:33 GMT

[quote][b]rondad (8/26/2009)[/b][hr]Has anyone tested the script with using a unc path for the new target location rather than a "hard-coded" drive letter?Ron[/quote]I have not tested it like that.

RE: Moving Databases with the ALTER Command

imSQrLy — Wed, 26 Aug 2009 12:48:57 GMT

[quote][b]Private Gripweed (8/26/2009)[/b][hr]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 + '")'[/quote]Thanks for pointing that out. I will update my script.

RE: Moving Databases with the ALTER Command

imSQrLy — Wed, 26 Aug 2009 12:47:54 GMT

[quote][b]Johan van Tonder (8/26/2009)[/b][hr]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 ;-)[quote]Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE[/quote]Yes, I know what's being meant, but that gave me laugh though :-)[/quote]Bang your dead!I haven't laughed like this all week.

RE: Moving Databases with the ALTER Command

K. Brian Kelley — Wed, 26 Aug 2009 12:11:31 GMT

EDIT: Brian needs to read more closely. :)

RE: Moving Databases with the ALTER Command

Private Gripweed — Wed, 26 Aug 2009 11:54:35 GMT

Easily tested. But I have not

RE: Moving Databases with the ALTER Command

rondad — Wed, 26 Aug 2009 11:41:14 GMT

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

RE: Moving Databases with the ALTER Command

Private Gripweed — Wed, 26 Aug 2009 11:27:28 GMT

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

RE: Moving Databases with the ALTER Command

Johan van Tonder — Wed, 26 Aug 2009 11:13:01 GMT

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 ;-)[quote]Keep in mind this script will kill any connected users by setting the db to single user with SET SINGLE_USER WITH ROLLBACK IMMEDIATE[/quote]Yes, I know what's being meant, but that gave me laugh though :-)

RE: Moving Databases with the ALTER Command

Private Gripweed — Wed, 26 Aug 2009 10:12:40 GMT

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

Moving Databases with the ALTER Command

imSQrLy — Wed, 26 Aug 2009 00:18:09 GMT

Comments posted to this topic are about the item [B]Moving Databases with the ALTER Command[/B]