SQL: Script to attach and detach Databases for mass moves
I dont know about most of you, but this is something that I have had to do on multiple occasions, moving databases to a new SAN/drive/etc. And the environments that I normally have to support have WAY too many databases to sit and detach, move, then reattach by hand. A lot of scripts out there arent very intuitive, and some other blogs I have read can be rather misleading.
The following scripts were originally borrowed from somewhere out there, over the rainbow, and tweak to meet the requirements that make my life easier. Unlike most scripts you will find, this one actually pulls out the filenames from the system database to help with locating, moving, and reattaching. (This is sort of important since a lot of applications create thier own databases, and being that there are do many, I would have to sit and document everything). Obviously, from this script, I only want databases that are on the M: drive, so the script will only pull out those values. Change that to whatever you want it to be. @DBPath and @LogPath are the new locations where I will be moving the files to.
--Declare variables
DECLARE @DBPath varchar(400)
DECLARE @LogPath varchar(400)
SET @DBPath = 'H:SQLDATA'
SET @LogPath = 'I:SQLLOGS'
use [master]
--You can copy and past these results into a new query window for the re-attach script
Select 'EXEC sp_attach_db @dbname='''+ [name] + ''',',
'@filename1=''' + @DBPath + REVERSE(SUBSTRING(REVERSE(filename), 0, CHARINDEX('', REVERSE(filename), 1))) + ''',', +
'@filename2='''+ @LogPath + [name] +'_Log.ldf''' from sysdatabases
where dbid > 4 AND filename LIKE 'M:%'
Now this is only the step for creating the scripts to re-attach the databases. The following is the detach script.--Code to generate detach script
Select 'EXEC sp_detach_db ''' + [name] + '''' from sysdatabases where dbid > 4 AND filename LIKE 'M:%'
I hope this helps someone out, it has sure saved my butt some work a few times.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader or join our newsletter and receive future articles emailed directly to you.
Comments
No comments yet.
Leave a comment