Generate CREATE DATABASE FOR ATTACH for all databases on server
-
Comments posted to this topic are about the item Generate CREATE DATABASE FOR ATTACH for all databases on server
-
Quite good, but there's a small problem: in a few places where specifying the @dbname you should enclose it in [], to avoid problems when dbname is contains spaces... like [register database] for example.
Cheers
LV
-
good script and most useful for DR purposes wesley. good timing as well, I have a script to reverse engineer sp_attach_db statements, but expecting some database with more than 16 files (the limit atachdb can cope with) I need to script to do the same thing but via create...for attach.
I expect sp_attach_db will also be deprecated in the future so this is the way to go.
to make the output more readable I would add some char(13) to wrap the text in the following places:
IF @@FETCH_STATUS = 0
SET @sql = @sql + ','+char(13)
END
CLOSE DetachData
DEALLOCATE DetachData
SET @sql = @sql +char(13)+' LOG ON '
DECLARE DetachData CURSOR FOR
SELECT [File], [Size], [Growth], [Name], [Group]
FROM tempdb..DetachData
WHERE FileType = 'L'
OPEN DetachData
FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + 'NAME = ''' + RTRIM(@name) + ''','
SET @sql = @sql + 'FILENAME = ''' + RTRIM(@file) + ''','
SET @sql = @sql + 'SIZE = ' + @size + ','
SET @sql = @sql + 'FILEGROWTH = ' + @growth
FETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @group
IF @@FETCH_STATUS = 0
SET @sql = @sql + ','+char(13)
END
CLOSE DetachData
DEALLOCATE DetachData
SET @sql = @sql +char(13)+' FOR ATTACH '
---------------------------------------------------------------------
-
..and I should have said thanks for sharing.
---------------------------------------------------------------------
-
Thanks for the feedback! I'll make the changes and post them back up.
Wes
-
Thanks for this script - see below (edited)
-
Thanks for this script - almost exactly what I was looking for. I do have one problem, though:
I need to run this for 50 very large databases to move the files to a new drive on the same box. Works great for 48, but two of the generated CREATE strings actually are longer than 8000 chars. I set the output in my query window for 8192 text, and declared the @sql variable to (max) but it still will not print anything past the 8000th character. What to do??
Thanks much for an otherwise excellent script.
kjt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply