How can I copy a database from a stored procedure

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.

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 .  


...

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.

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.

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

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.

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.

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

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.

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

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.

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.

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 !!

 

I will look at it, I got the same results yesterday.
I will also enhance the procedure to be 100% secure.
But later ...

thank you ispaleny.  I will continue to watch this site for your updates.

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

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 !

with nowait messages are ugly, but they can be used to simulate "progress bar".

very nice ispaleny - thank you.

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?

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 :)

ok, I get it.  And if I leave everything on the PRIMARY filegroup, then the database will always have only 2 files.  Correct?

If you have only one file in your PRIMARY filegroup, YES.