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.