Enumerate, Detach and Copy SQL Server Databases

Environment: MS SQL Server 7.0

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

Note: all sources are presented above. These sources are too dangerous to spoon-feed, and as such, any deployment is the responsibility of the user.


About the Author

Mark McGinty

Programming professionally for 15 years I've used a wide range of languages, including C/C++, x86 assembler, JScript, numerous forms of VB, SmallTalk and T-SQL, to name a few. Much of what I do these days involves databases to some degree, mostly back-end database/application architecture for data-driven websites.

Comments

  • sadfds

    Posted by Duong on 11/30/2004 08:56am

    dfsds

    Reply
  • HOW to extract the things using SQL statements

    Posted by Legacy on 02/20/2003 12:00am

    Originally posted by: umesh

    hi
    I want to exract the certain things using SQL statements
    can any body help me???
    Iam using Microsoft access 2000
    my query is

    price Condition vaule
    ----- -------- -----
    1000 < 200
    102 > 1200
    3000 = 3000
    4000 >= 1000

    I want to Extract the PRICE depending on Condition.

    i.e if the Value is <(condition) 1000 then Price value should disply(etract) else not.similarly for all the conditions

    so please help me

    umesh

    Reply
  • WARNING!! Danger if Replication is enabled.

    Posted by Legacy on 12/23/1999 12:00am

    Originally posted by: Giampaolo Papotti

    You should also check for distribution db (it's a SYSTEM DATABASE created only if you are a publisher!!)
    Furthermore, you SHOULD NOT copy a database this way if it owns replication subscriptions!!!!!

    Reply
  • Good code (works) but....

    Posted by Legacy on 12/22/1999 12:00am

    Originally posted by: Brian V. Shifrin


    I failed to see what it has to do with C++ or MFCs?

    Do I need proC to run it :)

    Reply
  • DANGER :)

    Posted by Legacy on 12/21/1999 12:00am

    Originally posted by: Allan Nielsen

    When ever I execute your code, the building next door explodes, is this a bug or a feature ?

    The code works fine, its just that I have expeced a big bang with all them warnings about DANGER ;-)

    regards, Allan

    • Fair disclosure

      Posted by mmcginty on 07/29/2006 03:35am

      Building demolition: bug or feature? Depends on how much you like your next door neighbor.

      Warnings gone wild? No, not at all; merely fair notice of potential for damage, with strong suggestion to casual readers to give it a miss unless they understand it.

      For instance, it will detach a multi-file db, but there's a good chance it will fail to reattach it. This problem is easily fixed -- if you know what to do.

      Further, in this sue-happy world of ours, only an idiot would offer something inherently dangerous without including a healthy dolop of CYA.

      So please heed the warnings, the danger is real... the data you save may be your own.

      -MM

      Reply
    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

Most Popular Programming Stories

More for Developers

RSS Feeds