Help with Duplicating a Database Via a Stored Proc

[bluetrane]

Okay, so I am trying to script a Stored Procedure in MS SQL that will dynamically create a new database and copy all the objects from an existing database into it.

I will be invoking this script from a web page. So far I have this:

create procedure sp_createInstallerDB @databasename varchar(128) as
declare @query varchar(1000)
set @query = 'create database '+ @databasename
exec (@query)
GO

Which dynamically creates a DB with the name I provide. What is the easiest way to copy the objects and data into it from my existing database called "installer"?

The simple objective here is to basically duplicate a database and give it a new name. Any help would be greatly appreciated! Thank you!

[skhanal]

If you can detach the source database, then fastest and easiest is to use sp_detach_db, copy files to new location, sp_attach_db the source and sp_attach_db the copy.

Other option is to backup existing database and restoring it as a new database.

Third option is to create a new empty database and creating a DTS package to move all objects and calling that DTS from our SP.

[bluetrane]

skhanal thanks for your response. I'm afraid, I'm somewhat of a newbie to MS SQL and need a bit more help if you can offer it:

I would like my stored procedure to create the database with the name I supply like this:

create procedure sp_createInstallerDB @databasename varchar(128) as
declare @query varchar(1000)
set @query = 'create database '+ @databasename
exec (@query)
GO

Then I would like to restore from another file to populate my new database with the back-up objects and data. Is this possible in a Stored Proc, and if so, what modifications would I have to make to my script above?

[MAK]

when you say "FILE" is it a backup file?

if it is a backup file then

you can restore the database with new name (no need to create a database. it will create automatically when u restore with new name) but u need to know the logical file names. (use restore headeronly option for this)

ex:


restore database TOPS from disk = 'd:\backup\TOPS_db.BAK' with replace,
move 'Tops_Data' to 'D:\Program Files\Microsoft SQL ServerData\TOPS_Data.MDF',
move 'Tops_Log' to 'D:\Program Files\Microsoft SQL ServerData\TOPS_Log.LDF'

[bluetrane]

Originally posted by MAK

ex:

restore database TOPS from disk = 'd:\backup\TOPS_db.BAK' with replace,
move 'Tops_Data' to 'D:\Program Files\Microsoft SQL ServerData\TOPS_Data.MDF',
move 'Tops_Log' to 'D:\Program Files\Microsoft SQL ServerData\TOPS_Log.LDF'

MAK, thanks for your help with this. I tried using your example as follows:

restore database myNewDB from disk ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\data2BRestored.BAK' with replace,
move 'myNewDB_data' to 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\myNewDB_data.MDF',
move 'myNewDB_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\myNewDB_log.LDF'

and I got the following error...

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'myNewDB_data' is not part of database 'myNewDB'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What am I missing? I don't really understand what the move command is supposed to accomplish. Thanks!

[skhanal]

When you restore a database to a new name using a backup file, the logical file name must match as in the backup file. (You can change it later using ALTER DATABASE)

Use the logical name of your original database instead of 'myNewDB_data' and 'myNewDB_log'

[bluetrane]

Originally posted by skhanal
When you restore a database to a new name using a backup file, the logical file name must match as in the backup file. (You can change it later using ALTER DATABASE)

Use the logical name of your original database instead of 'myNewDB_data' and 'myNewDB_log'

skhanal, thanks for your help with this. Okay, so I got past that error by making the changes:

restore database myNewDB from disk ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\i3installer_v40\i3installer_v4 0.BAK' with replace,
move 'myNewDB' to 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\myNewDB_data.MDF',
move 'myNewDB' to 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\myNewDB_log.LDF'

Now I get this error:

Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Is there some command to drop all connections to the database so that I can restore it?

[skhanal]

You can run

ALTER DATABASE myNewDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I thought you were restoring it as a new database.

[MAK]

use the proc usp_killprocess
before retoring any database.


http://www.databasejournal.com/featu...le.php/2174031

[bluetrane]

First of all thank you MAK and skhanal for all of your patience and help. OK. Here's the problem:

We've built our own in house Content Management System, and we're trying to create an installer. All is good until we get to the part where we 'create' the database from our database 'source'.

We have a 'source' database, that, using web programming, we want to copy/replicate into a new, running, attached and usable database on the same server.

As seen from previous posts, you can see that we've tried both the attach/detach method, as well as the 'restore to' method, both without complete success...
Here's what we've tried:

Attach/Detach Method:
1. Detach the source db
2. Copy the source.mdf file and rename to newdb.mdf (VIA ColdFusion web script)
3. Attach newdb
4. Re-attach source db (fails)

The result:

"The physical file name '[path]\source_log.ldf' may be incorrect"

The above method worked for us as long as we leave the source db detached. The
problem is, we need the source db to be a functioning datasource on the server.

Restore Method:
restore database newdb10 from disk ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\i3installer_v40\i3installer_v4 0.BAK' with replace,
move 'i3installer_v40' to 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\newdb10_data.MDF',
move 'i3installer_v40_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL\DATA\newdb10_log.LDF'

The result:

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'i3installer_v40' is not part of database 'newdb10'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

We've been struggling with this issue for a few days now, and just don't know where to turn. We have learned a lot and gotten some great ideas from this forum, but nothing that completely works. Please help! What is your best recommendation?

[skhanal]

Attach db method -

Did you copy the log file to a new location and specified in your sp_attach_db statement?

I am suspecting that both old and new db are trying to use the same log file.

Restore -
What are the logical file names for the orignal database? See whether

sp_helpdb orignaldb

restore filelistonly from disk=''c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\i3installer_v40\i3installer_v4
0.BAK'

return the same logical db.

[MAK]

use "sp_attach_single_file_db" for attaching only .mdf file, so that sql server creates a .ldf file automatically.

Before copying the .mdf file do the following.

1. use databasename
2. checkpoint
3. sp_detach_db databasename
4. copy the .mdf file to the new location

if you want to go ahead with the backup restore process.

example:

1. backup database SNAR to disk ='d:\SNAR.bak' with init
2. restore filelistonly from disk ='d:\SNAR.bak'
results:

SNAR_Data D:\Program Files\Microsoft SQL Server\MSSQL\Data\SNAR_Data.MDF D PRIMARY 5242880 35184372080640
SNAR_Log D:\Program Files\Microsoft SQL Server\MSSQL\LOG\SNAR_Log.LDF L NULL 12648448 35184372080640

3. restore database snar2 from disk='d:\snar.bak' with replace,
move 'snar_data' to 'd:\snar2_data.mdf',
move 'snar_log' to 'd:\snar2_log.ldf'

Note: if your sql server is case sensitive then follow the same case returned by the filelistonly.