Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving Databases with the ALTER Command Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 12:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
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
Posted Wednesday, August 26, 2009 10:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 9, 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
Posted Wednesday, August 26, 2009 11:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 4, 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
Posted Wednesday, August 26, 2009 11:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 10:36 AM
Points: 30, Visits: 130
LOL that is funny, I did not think about it that way.
Post #777691
Posted Wednesday, August 26, 2009 11:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:59 AM
Points: 13, Visits: 25
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
Posted Wednesday, August 26, 2009 11:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 10:36 AM
Points: 30, Visits: 130
Easily tested. But I have not
Post #777718
Posted Wednesday, August 26, 2009 12:11 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 8:57 AM
Points: 6,634, Visits: 1,872
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
Posted Wednesday, August 26, 2009 12:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
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
Posted Wednesday, August 26, 2009 12:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
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
Posted Wednesday, August 26, 2009 12:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:20 PM
Points: 133, Visits: 472
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 »

Add to briefcase 12»»

Permissions Expand / Collapse