Generate CREATE DATABASE FOR ATTACH for all databases on server

Question

Generate CREATE DATABASE FOR ATTACH for all databases on server

Wesley Brown

SSChampion

Points: 12045
More actions
November 20, 2008 at 4:12 pm

#215543

Comments posted to this topic are about the item Generate CREATE DATABASE FOR ATTACH for all databases on server

Viewing 7 posts - 1 through 6 (of 6 total)

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

Luigi Visintin SSC Eights! Points: 822 More actions · Answer 1

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

george sibbald SSC Guru Points: 104210 More actions · Answer 2

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 + '('

SET @sql = @sql + 'NAME = ''' + RTRIM(@name) + ''','

SET @sql = @sql + 'FILENAME = ''' + RTRIM(@file) + ''','

SET @sql = @sql + 'SIZE = ' + @size + ','

SET @sql = @sql + 'FILEGROWTH = ' + @growth

SET @sql = @sql + ')'

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 '

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 3

..and I should have said thanks for sharing. 🙂

---------------------------------------------------------------------

Wesley Brown SSChampion Points: 12045 More actions · Answer 4

Thanks for the feedback! I'll make the changes and post them back up.

Wes

zipman1952 SSC Journeyman Points: 95 More actions · Answer 5

zipman1952

SSC Journeyman

Points: 95

November 4, 2009 at 12:09 pm

#1074974

Thanks for this script - see below (edited)

zipman1952 SSC Journeyman Points: 95 More actions · Answer 6

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