?
Solved

How can I copy a database from a stored procedure

Posted on 2003-03-27
24
Medium Priority
?
395 Views
Last Modified: 2007-12-19
I have a database with many tables, and each table has many columns.  There are also many indecies and primary keys.
From program control, I need to make a copy of the database (replicate the tables, columns, indecies, keys, etc.) with the copy having a different database name.
I would like to have a stored procedure that can copy the database, but I do NOT want to hard code all of the table names, columns, etc. into the stored procedure.  I want the stored procedure to get the table names, column names, column data types, etc. from the original database.  In this way, I can make changes to the original database, and I will not have to make changes to the stored procedure.

Is this possible? How is it done?

Thank you, in advance, for your help.


0
Comment
Question by:plevintampabay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 2
  • +3
24 Comments
 
LVL 1

Expert Comment

by:JWT
ID: 8220375
Use DTS - it can copy all the database objects you need.  You can call a DTS package from a stored procedure - or from a job.  Open DTS and look at the copy database objects task.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 8220433
why do you want the copy?

You could use snapshot replication to create a full copy of the database...

Alternatively have a look at the stored procedures that are used to build the snapshot/replication scenarios...
and use them as the basis for what you want...

Again stepping back from this...
shouldn't you be generating your database ddl , from your
data model / tool .  


...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8220687
I think you could also have a script that would take a backup of the existing database, compress and transfer the backup, then start a job on the other server to restore the backup.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:plevintampabay
ID: 8221005
If there is one thing your comments have told me, is -- I don't know much about SQL Server.

I mentioned that I wanted to make the copy of the database to a new database.  Let me add, that the new database is in the same SQL server, and will have a new name.  For example, if the original was called "Template", then the copies might be called "Instance1", "Instance2", etc.  Let me also mention that the method used to make the copy must be invocable from an ASP, using ADO.  That is why I thought a stored procedure was the way to go.

JWT - what is DTS? Is it "Data Transformation Services"? I opened that section in the SQL Server Enterprise Manager, and there are no packages there.

Lowfatspread - I really can not go into why I need to make a copy.  Where do I find the stored procedures for snapshot replication?  Can snapshot replication be used from ASP/ADO?

ScottPletcher - the idea of a backup and restore sounds interesting.  Are there SQL commands that can be run in a stored procedure, to do the restore.  The original backup I can do manually.  Note that the copy will be in the same server, so there is no need to compress and transfer.

Again, thank you very much for you help.  Please continue.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 8221127
DTS is Data transformation services...
 you can script command and save them as packages,
 which can them be run and i think invoked ...

Replication possibly not the way to go.
   but the best way of getting the stored procs is
   by setting up repkication and seeing the procs which
   are produced. both in your source database and
   the distribution database..

   snapshot replication can be scheduled.. you could
    certainly trigger the jobs from a stored proc.
     setting up new databases and pointing the snapshot
      to them would probably be a clumsy solution..

Backup/restore sounds more like what you want
I've never embedded them in a stored proc, it more
likely i'd run the command via ISQL / OSQL
nut the syntax is readily available in BOL


hth got to dash..
0
 
LVL 1

Expert Comment

by:JWT
ID: 8221198
As LFS mentioned DTS is Data Transfer Systems.  When you open up a connection it is listed right below Databases.  What you need to do to look at a package is right click on the folder and select new package.

When the package opens on the right hand side you will see 'Connection' with some icons underneath then 'Tasks' with more icons.  You want to look under tasks.  Specifically the Transfer Databases tasks and the Copy SQL Server Objects Task.  If you hover the mouse pointer over the icon a popup tells you their name.

So one you are this far, I can best help you by sending you to http://www.sqldts.com/.  Look up Transfer Databases task there.

The problem with backup and restore is that you can't move other database objects to your new server.  So triggers stored procedures, etc would have to be scripted and recreated.
0
 

Expert Comment

by:Ronald_Bijl
ID: 8223304
Guys,

I think DTS, replication and logshipping is not the answer here. Why don't you just write a stored proc that dumps the original database to a local drive, the kill all the connections with the destination database and last but not least restore the previously made dump into the destination database.

Good luck.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8223401
The question is: Do you need to replicate data or structures only ?

For data:
---------
Ronald_Bijl's suggestion: BACKUP->RESTORE
=2xcoping database
Or you can use faster: DETACH->COPY->2xATTACH
=1xcoping database

For killing user connections:
-----------------------------
isql.exe + KILL command

For structures:
-----------------------
DTS, SQLDMO objects


0
 

Author Comment

by:plevintampabay
ID: 8226258
Wow, you guys are awesome !

After reading your comments, I've spent some time reading the SQL Server on-line help (not great, but does inform a bit).
I've also been reading from http://manuals.sybase.com/onlinebooks/group-as/srg1100e/sqlref/@Generic__BookTextView/66 .

Since I have to invoke the database copy from ADO, it looks like my best approaches are:
  1) "dump database" and "load database". Are these the commands that you guys are refering to, when you say "Backup and Restore" ?
  2) "sp_detach_db" and "sp_attach_db".  I assume these are the system stored procedures that ispaleny is refering to, when he/she mentions DETACH and ATTACH.

To answer ispaleny's question, the database that I am copying may have some (very minimal) data in it.  The most important thing being copied is the structure - tables, columns, column data types, and possibly a few stored procedures.

I have tried to use both of the approachs mentioned above, and have run into problems with both of them.  Maybe you guys can help me get past the problems.

1) using "dump database" and "load database" --
  a) I tried the command

           dump database originalDB to "C:\dumptest"

     and got the error -

No entry in sysdevices for backup device 'C:\dumptest'. Update sysdevices and rerun statement.

  c) Does this mean that in order to do a dump to a file, I have to put that file into a system table.  This is not a great thing, since it means that for my code to be installed on a SQL server I have to modify that server's system tables for my code to run.  I don't like that idea, but if it is the only way, then I guess it will have to do.


2) using "sp_detach_db" and "sp_attach_db" --
  a) I can detach the originalDB database.  
  b) I found the files

          originalDB_Data.MDF   and
          originalDB_Log.LDF    in the folder
          C:\SQL Server\MSSQL$SQLSERVER1\Data  

  c) I made a copy of both of these files, and called them

          copyDB_Data.MDF       and
          copyDB_Log.LDF

  d) When I tried to attach them as a new database called "copyDB" I got an error -

          The LSN (5:423:1) passed to log scan in database 'copyDB' is invalid.


Can you guys help me get past these errors?
I like the detach/attach approach, but in addition to getting past the error, I need to programatically be able to find and copy the files resulting from the detach.  Is there some SQL variable that tells me the path to the SQL Data folder, or at least the path of where the SQL server is installed?

Again THANK YOU very much for all of your help !!
Please continue.
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 800 total points
ID: 8232780
--drop database Test666
--drop database Test667
create database Test666
GO
use Test666
GO
declare @NewDBName nvarchar(128)
declare @DOSEXEC nvarchar(4000)
declare @EXEC1 nvarchar(4000)
declare @EXEC2 nvarchar(4000)
declare @Counter int
declare @Paths nvarchar(4000)
declare @NewPaths nvarchar(4000)
declare @dbid int

set @NewDBName='Test667'

declare @Cnt smallint
declare @sysfiles table(     fileid smallint identity(1,1) primary key,oldfilename nvarchar(260),newfilename nvarchar(260) )
insert @sysfiles(oldfilename,newfilename)
select rtrim([filename])
,reverse(stuff(reverse(rtrim([filename]))
,charindex('\',reverse(rtrim([filename]))) ,1,+'_'+convert(varchar(36),newid())+'_ypoC\'))
from dbo.sysfiles
order by fileid
set @Cnt=@@ROWCOUNT
set @Paths=''
set @NewPaths=''
set @Counter=1
select
 @Paths   =@Paths   +', @filename'+cast(@Counter as varchar(7))+'=N'+QUOTENAME(OldFileName,'''')
,@NewPaths=@NewPaths+', @filename'+cast(@Counter as varchar(7))+'=N'+QUOTENAME(NewFileName,'''')
,@Counter=@Counter+1
from @sysfiles
order by fileid
set @EXEC1='EXEC sp_detach_db '+QUOTENAME(DB_NAME(),'''')+', ''true'''
set @EXEC2=
+'EXEC sp_attach_db @dbname = N'+QUOTENAME(DB_NAME(),'''') +@Paths
+'EXEC sp_attach_db @dbname = N'+QUOTENAME(@NewDBName,'''')+@NewPaths
set @dbid=db_id()
USE MASTER
set @DOSEXEC=(select top 1 spid  from master.dbo.sysprocesses where spid<>@@spid and dbid=@dbid)
while @DOSEXEC is not null begin
 set @DOSEXEC='isql -E -dmaster -Q "KILL '+@DOSEXEC+'"'
 select @DOSEXEC
 exec master.dbo.xp_cmdshell @DOSEXEC, no_output
 set @DOSEXEC=(select top 1 spid  from master.dbo.sysprocesses where spid<>@@spid and dbid=@dbid)
end
EXEC(@EXEC1)
set @Counter=1
while @Counter<=@Cnt begin
 select @DOSEXEC='copy /b "'+OldFileName+'" "'+NewFileName+'"'
 from @sysfiles where fileid=@Counter
 exec master.dbo.xp_cmdshell @DOSEXEC, no_output
 set @Counter=@Counter+1
end
EXEC(@EXEC2)






0
 

Author Comment

by:plevintampabay
ID: 8238849
Wow ispaleny, that is awesome.

Can you give me a brief explanation of what all of that code is doing.  As you might have guessed, I am not very familiar with the SQL scripting language.

  Thanks,
  Paul
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8239171
It duplicates the current database to a new database.

A brief description
------------------------------------------------------
1. Create database Test666
2. Switch to Test666
3. Generate old+new database filenames
5. Switch to master
4. Disconnect all users from Test666
( SQL server must be running under WinAuth-SQL admin account (administrator in a standard installation) )
5. Disconnect Test666 database from the server
6. Copy Test666 database files to new database files
7. Database Test666 is connected back to the server
8. New database Test667 is connected to the server
9. Done
-------------------------------------------------------

A little bit unsecure, but with no data loss.
5 minutes solution for 1 GB database :)

For your database replace Test666 by your database name
and Test667 by your new database name.


0
 

Author Comment

by:plevintampabay
ID: 8239672
That is really great ispaleny.

I assume that all of the code below the second GO, can be put into a Stored Procedure that can be invoked from an ASP.  I'll need to change the line
   @NewDBName='Test667'
to use a parameter to the Stores Procedure.
The procedure will be invoked from the source database, so that should take care of the "use" statement, which I believe can not be used in a stored procedure.

Great.  I'll play with it later today, and let you know how it goes.

Thank you.
0
 

Author Comment

by:plevintampabay
ID: 8241827
ispaleny,
  I just tried your script in the Query analyzer.
  I get to the first while loop, which kills users off of the source database (correct me if I'm wrong).  But I never get out of that loop.
  So I commented out that loop.  
  Then it works great !!  I change the value of @NewDBName, and in seconds, I had a copy of the source database.

  So now I need to deal with putting it into a stored procedure that uses a parameter for the name of the new database.  And also solve the problem of not getting out of that first while loop.

  Any ideas on the while loop problem?

  Thanks again !!

 
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8244632
I will look at it, I got the same results yesterday.
I will also enhance the procedure to be 100% secure.
But later ...
0
 

Author Comment

by:plevintampabay
ID: 8245847
thank you ispaleny.  I will continue to watch this site for your updates.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8246304
DROP PROC USP_CopyDatabase
GO
CREATE PROC USP_CopyDatabase(@DBName nvarchar(128),@NewDBName nvarchar(128)) AS BEGIN

declare @DOSEXEC nvarchar(4000)
declare @EXEC1 nvarchar(4000)
declare @EXEC2 nvarchar(4000)
declare @EXEC3 nvarchar(4000)
declare @Counter int
declare @Paths nvarchar(4000)
declare @NewPaths nvarchar(4000)
declare @Cnt smallint
declare @dbid int

if is_srvrolemember('sysadmin')=0 begin
 raiserror('Sorry my dear user.',16,1) with log
 return
end
if @DBName=db_name() begin
 raiserror('The actual database cannot be copied.',16,1)
 return
end
if not exists(select * from master.dbo.sysdatabases where [name]=@DBName) begin
 raiserror('The source database does not exist.',16,1)
 return
end
if exists(select * from master.dbo.sysdatabases where [name]=@NewDBName) begin
 raiserror('The target database exists.',16,1)
 return
end

create table #sysfiles
(
fileid smallint identity(1,1) primary key
,oldfilename nvarchar(260)
,newfilename nvarchar(260)
)

set @dbid=db_id(@DBName)
declare @insexec nvarchar(4000)
set @insexec='
select rtrim([filename])
,reverse(stuff(reverse(rtrim([filename]))
,charindex(''\'',reverse(rtrim([filename]))) ,1,''_''+replace(convert(varchar(36),newid()),''-'','''')+''_ypoC\''))
from "'+REPLACE(@DBName,'''','''''')+'".dbo.sysfiles order by fileid
'
insert #sysfiles(oldfilename,newfilename)
EXEC(@insexec)
set @Cnt=@@ROWCOUNT
set @Paths=''
set @NewPaths=''
set @Counter=1
select
 @Paths   =@Paths   +', @filename'+cast(@Counter as varchar(7))+'=N'+quotename(OldFileName,'''')
,@NewPaths=@NewPaths+', @filename'+cast(@Counter as varchar(7))+'=N'+quotename(NewFileName,'''')
,@Counter=@Counter+1
from #sysfiles
order by fileid
set @EXEC1='exec sp_detach_db '+quotename(@DBName,'''')+', ''true'''
set @EXEC3=
+'exec sp_attach_db @DBName = N'+quotename(@DBName,'''') +@Paths
+'exec sp_attach_db @DBName = N'+quotename(@NewDBName,'''')+@NewPaths
raiserror('Disconnecting users',1,1) with nowait
set @EXEC2=(select top 1 spid  from master.dbo.sysprocesses where spid<>@@spid and dbid=@dbid)
WHILE @EXEC2 is not null BEGIN
 set @EXEC2='KILL '+@EXEC2
 exec(@EXEC2)
 set @EXEC2=(select top 1 spid  from master.dbo.sysprocesses where spid<>@@spid and dbid=@dbid)
END
raiserror('Creating an auto-recover procedure',1,1) with nowait
if object_id('dbo.SSP_UserDatabaseAutoRecover') is not null drop procedure dbo.SSP_UserDatabaseAutoRecover
exec('create proc dbo.SSP_UserDatabaseAutoRecover as '+@EXEC3+' DROP PROC dbo.SSP_UserDatabaseAutoRecover')
exec sp_procoption @ProcName ='dbo.SSP_UserDatabaseAutoRecover', @OptionName = 'startup', @OptionValue = 'on'
raiserror('Disconnecting the source database',1,1) with nowait
exec(@EXEC1)
raiserror('Copying the source database. This can take some time :)',1,1) with nowait
set @Counter=1
while @Counter<=@Cnt begin
 select @DOSEXEC='copy /b "'+OldFileName+'" "'+NewFileName+'"'
 from #sysfiles where fileid=@Counter
exec master.dbo.xp_cmdshell @DOSEXEC, no_output
set @Counter=@Counter+1
end
raiserror('Connecting databases',1,1) with nowait
exec(@EXEC3)
raiserror('DONE',1,1) with nowait

END
GO
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8246412
This proceduse should be secure up to 6 files per database,
maybe more (limit of nvarchar-4000 chars).
Run it like this

use master
exec dbo.USP_CopyDatabase @DBName ='Test', @NewDBName='TestCopy'

If an error occurs, run
exec dbo.SSP_UserDatabaseAutoRecover
or this SP will be auto-run after restart.

The insecurity is in disconnecting a database. You must know where your database files were to be able to attach them manually, if dbo.SSP_UserDatabaseAutoRecover SP fails.

If you want a secure procedure, use BACKUP/RESTORE, but is is at least 2x slower.

Good luck !
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8246418
with nowait messages are ugly, but they can be used to simulate "progress bar".
0
 

Author Comment

by:plevintampabay
ID: 8247343
very nice ispaleny - thank you.
0
 

Author Comment

by:plevintampabay
ID: 8247664
ispaleny - I understand the limitation you mentioned, about 6 files per database (actually whatever consumes the 4000 characters).  In addition, the sp_attach_db has a limitation of 16 files.

What in the database causes there to be more than the current 2 files?  Right now, I get a .MDF and a .LDF file.  Why would any more than that exist?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8248669
Every object (table,PK,index,BLOB,...) is stored on a filegroup. Each filegroup can consist of multiple files.

The filegroups can be used to:
1. Make some data read-only.
2. Separate objects
3. Increase speed by accessing multiple disks
4. Make a partial database backup
5. I am sure, you will find other usage also :)
0
 

Author Comment

by:plevintampabay
ID: 8248732
ok, I get it.  And if I leave everything on the PRIMARY filegroup, then the database will always have only 2 files.  Correct?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8248802
If you have only one file in your PRIMARY filegroup, YES.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question