Enumerate, Detach and Copy SQL Server Databases

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

Downloads