This code will attempt to detach, copy and reattach all databases of a SQL Server. If successful the copied databases will suitable for attachment to any other SQL Server. This can be useful when upgrading servers, or when a duplicate copy of a database is needed for other reasons
It is also a fair example of the use of cursors in a stored procedure.
At the risk of restating the comments, THIS IS DANGEROUS CODE! Please read the comments carefully before attempting to use it. You should fully understand the constructs in use before attemting to deploy this code.
/* ** ** Programmer: Mark J. McGinty ** Project Mgr: Robyn Ise ** ** Developed under contract for 1LINK, Inc ** ** Permission to Publish Courtesy of Robyn Ise; 1LINK, Inc ** ** WARNING: THIS IS DANGEROUS CODE! ** IT SHOULD BE USED ONLY IN COMBINATION WITH A SUBSTANTIAL ** UNDERSTANDING OF THE CONSTRUCTS EMPLOYED HEREIN! ** ** DISCLAIMER: Some of the constructs demonstrated by this ** code are inherently dangerous. User assumes any and all ** consequences and/or liability for any damage caused directly ** or indirectly by the use of this code, or any derivative ** work. In no event will the programmer, or the developer ** listed above, be held responsible for any damages of any ** sort. ** ** BY USING ANY PORTION OF THIS CODE, YOU IMPLICITLY AGREE TO ** THE TERMS OF THIS DISCLAIMER. ** ** ** NOTES, CAVEATS, AND CONDITIONS: ** ------------------------------ ** READ THIS CODE CAREFULLY, AND UNDERSTAND WHAT IT DOES, ** BEFORE RUNNING IT! ** ** The Books Online (BOL) are your friends! ** ** This code was developed to run on MS SQL Server 7.0, and has ** been tested with and without SP1. It will not run on MS SQL ** Server 6.5. (Exactly why we neither know nor care.) ** ** Always make a database backup before experimenting with ** dangerous code. ** ** All connections to a database must be closed before it can ** be detached. ** ** Object-level permissions will be stripped from the file ** copies, and in some cases may be stripped from the original ** database as well. Users and permissions should be scripted ** before running this code. ** ** ** Purpose: This code will attempt to detach, copy and ** reattach all databases of a SQL Server. If successful ** the copied databases will suitable for attachment to ** any other SQL Server. ** */ CREATE PROCEDURE sp_detach_and_copy_all_dbs ( @targetdir varchar(260) ) AS declare @filename varchar(260) declare @dbname sysname declare @shellcmd varchar(260) declare @beforecount integer declare @aftercount integer declare @tmpvarchar varchar(260) /* ** Count existing databases, make sure we end up with what ** we started. */ set @beforecount= (select COUNT([name]) from sysdatabases) print @beforecount /* ** Create a cursor, iterate through all records. */ declare dbc cursor for select [name], [filename] from sysdatabases open dbc fetch dbc into @dbname, @filename while @@fetch_status >= 0 begin /* ** Skip over system db's since we wouldn't want to copy them, ** even if we could. */ if @dbname in ('master', 'msdb', 'tempdb', 'pubs', 'model') begin print '(skipping system db)' end else begin print @dbname /* ** Prepare a shell command string to copy the file, after ** it's detached. */ set @shellcmd = 'copy ' + @filename + ' ' + @targetdir /* ** Make sure we are able to detach the database. */ exec sp_certify_removable @dbname, 'auto' if @@error = 0 begin exec sp_detach_db @dbname end else begin set @tmpvarchar = 'FAILED to detach ' + @dbname raiserror (@tmpvarchar, 11, 11) end if @@error = 0 begin exec xp_cmdshell @shellcmd /* ** This could be more elegant, it would be better to ** enumerate the contents of @dbname.dbo.sysfiles, create ** an argument list, and call sp_attach_db (which accepts ** a variable number of args, up to 16 file names.) ** ** In practice you could usually get away with expecting ** two files (a db and a transaction log), but that has a ** chance of failing, and you'd be better off using ** sp_attach_single_file_db, rather than trying to attach ** with an incomplete list. */ exec sp_attach_single_file_db @dbname, @filename if @@error <> 0 begin set @tmpvarchar = 'Attach FAILED:' + CAST(@dbname AS varchar) + ' (' + @filename + ')' raiserror (@tmpvarchar, 11, 11) end end end fetch dbc into @dbname, @filename end deallocate dbc set @aftercount= (select count([name]) from sysdatabases) if @aftercount <> @beforecount begin set @tmpvarchar = 'database count different, was: ' + CAST(@beforecount AS varchar) + ' is: ' + CAST(@aftercount AS varchar) raiserror (@tmpvarchar, 11, 11) end return 0