SQLServerCentral / Discuss Content Posted by Wesley Brown / Article Discussions / Article Discussions by Author / Generate CREATE DATABASE FOR ATTACH for all databases on server / Latest Posts

RE: Generate CREATE DATABASE FOR ATTACH for all databases on server

zipman1952 — Wed, 04 Nov 2009 12:11:12 GMT

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

RE: Generate CREATE DATABASE FOR ATTACH for all databases on server

zipman1952 — Wed, 04 Nov 2009 12:09:32 GMT

Thanks for this script - see below (edited)

RE: Generate CREATE DATABASE FOR ATTACH for all databases on server

Wesley Brown — Thu, 18 Dec 2008 09:04:59 GMT

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

RE: Generate CREATE DATABASE FOR ATTACH for all databases on server

george sibbald — Thu, 18 Dec 2008 04:13:15 GMT

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

RE: Generate CREATE DATABASE FOR ATTACH for all databases on server

george sibbald — Thu, 18 Dec 2008 03:58:00 GMT

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 + ','[b]+char(13)[/b] ENDCLOSE DetachDataDEALLOCATE DetachDataSET @sql = @sql [b]+char(13)+[/b]' LOG ON 'DECLARE DetachData CURSOR FOR SELECT [File], [Size], [Growth], [Name], [Group] FROM tempdb..DetachData WHERE FileType = 'L'OPEN DetachDataFETCH NEXT FROM DetachData INTO @file, @size, @growth, @name, @groupWHILE @@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 + ','[b]+char(13)[/b] ENDCLOSE DetachDataDEALLOCATE DetachDataSET @sql = @sql [b]+char(13)+[/b]' FOR ATTACH '

RE: Generate CREATE DATABASE FOR ATTACH for all databases on server

Luigi Visintin — Thu, 18 Dec 2008 02:34:44 GMT

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.CheersLV

Generate CREATE DATABASE FOR ATTACH for all databases on server

Wesley Brown — Thu, 20 Nov 2008 16:12:35 GMT

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