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.
/* ** ** 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