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

Generate CREATE DATABASE FOR ATTACH for all databases on server Expand / Collapse
Author
Message
Posted Thursday, November 20, 2008 4:12 PM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Wednesday, July 23, 2014 1:21 PM
Points: 609, Visits: 407
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
Posted Thursday, December 18, 2008 2:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, December 18, 2008 3:58 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
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
Posted Thursday, December 18, 2008 4:13 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
..and I should have said thanks for sharing. :)

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

Post #621895
Posted Thursday, December 18, 2008 9:04 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Wednesday, July 23, 2014 1:21 PM
Points: 609, Visits: 407
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
Posted Wednesday, November 4, 2009 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:46 AM
Points: 15, Visits: 275
Thanks for this script - see below (edited)


Post #813785
Posted Wednesday, November 4, 2009 12:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:46 AM
Points: 15, Visits: 275
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 »

Add to briefcase

Permissions Expand / Collapse