Generate CREATE DATABASE FOR ATTACH for all databases on server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Wesley Brown » Generate CREATE DATABASE FOR ATTACH for all...

Generate CREATE DATABASE FOR ATTACH for all databases on server

Rate Topic

Display Mode

Topic Options

Author

Message

Wesley Brown

Posted Thursday, November 20, 2008 4:12 PM

SSChasing Mays

Group: Moderators
Last Login: Tuesday, May 07, 2013 3:54 PM
Points: 608, Visits: 379

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

http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org

Post #606164

Luigi Visintin

Posted Thursday, December 18, 2008 2:34 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 4:25 AM
Points: 96, Visits: 31

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

Post #621863

george sibbald

Posted Thursday, December 18, 2008 3:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 5,317, Visits: 11,307

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 '

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

Post #621887

george sibbald

Posted Thursday, December 18, 2008 4:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 5,317, Visits: 11,307

..and I should have said thanks for sharing. :)

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

Post #621895

Wesley Brown

Posted Thursday, December 18, 2008 9:04 AM

SSChasing Mays

Group: Moderators
Last Login: Tuesday, May 07, 2013 3:54 PM
Points: 608, Visits: 379

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

Wes

http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org

Post #622203

zipman1952

Posted Wednesday, November 04, 2009 12:09 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:20 PM
Points: 15, Visits: 272

Thanks for this script - see below (edited)

Post #813785

zipman1952

Posted Wednesday, November 04, 2009 12:11 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:20 PM
Points: 15, Visits: 272

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

Post #813788

« Prev Topic | Next Topic »

Permissions