SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate CREATE DATABASE FOR ATTACH for all databases on server


Generate CREATE DATABASE FOR ATTACH for all databases on server

Author
Message
Wesley Brown
Wesley Brown
Ten Centuries
Ten Centuries (1.2K reputation)

Group: Moderators
Points: 1153 Visits: 440
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
Luigi Visintin
Luigi Visintin
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 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
george sibbald
george sibbald
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13402 Visits: 13695
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
george sibbald
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13402 Visits: 13695
..and I should have said thanks for sharing. Smile

---------------------------------------------------------------------
Wesley Brown
Wesley Brown
Ten Centuries
Ten Centuries (1.2K reputation)

Group: Moderators
Points: 1153 Visits: 440
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
zipman1952
zipman1952
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 399
Thanks for this script - see below (edited)



zipman1952
zipman1952
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 399
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search