Creating a test copy of a database?

I was wondering if there is an easy way to "copy" an existing database,
complete with data, into a new one with a different name.
We have a complex SQL system that imports data from an external SQL source
(PervasiveSQL). We are in the process of upgrading that software to a much
newer version that has undergone major modifications, although I don't
_believe_ they impact the import process. I have already made a copy of the
PervasiveSQL database in the new format. What I would like to do now is copy
our SQLServer database, then run our importer against the two test databases.
Any advice?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
>
The easiest way usually is one of the following:
Backup the source database and then restore it with a different name. This
can be done in a production environment with no downtime.
Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Use sp_attach_db to attach the copied files with a different database name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hello,
You will have to BACKUP the database and Restore the database using a new
name.
1. Backup the database usuing BACKUP DATABASE command
2. Restore the database with new name specifying MOVE optiion.
take a look into BACKUP DATABASE and RESTORE DATABASE command with MOVE
option in Books online.
Thanks
Hari
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:D90FD511-6857-4573-94B6-60BEBE7ABB23@.microsoft.com...
>I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of
> the
> PervasiveSQL database in the new format. What I would like to do now is
> copy
> our SQLServer database, then run our importer against the two test
> databases.
> Any advice?
> Maury|||Maury Markowitz,
Take a full back of the db and restore it using a new database name and the
"with move" option. See "restore database" in BOL for more info.
AMB
"Maury Markowitz" wrote:
> I was wondering if there is an easy way to "copy" an existing database,
> complete with data, into a new one with a different name.
> We have a complex SQL system that imports data from an external SQL source
> (PervasiveSQL). We are in the process of upgrading that software to a much
> newer version that has undergone major modifications, although I don't
> _believe_ they impact the import process. I have already made a copy of the
> PervasiveSQL database in the new format. What I would like to do now is copy
> our SQLServer database, then run our importer against the two test databases.
> Any advice?
> Maury|||Backup and restore is the easiest method - see "Copying a database using
BACKUP and RESTORE" in BOL.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Alejandro Mesa" wrote:
> Take a full back of the db and restore it using a new database name and the
> "with move" option. See "restore database" in BOL for more info.
Thanks! I'll start working on this now.
Maury|||> Or, stop SQL Server, make a copy of the files and then restart SQL Server.
Personally I think it's safer to detach using sp_detach_db. Stopping SQL
Server will not necessarily correctly initiate the files for attaching to
another server (at least I've seen plenty of reports of such).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCCGX8BXHHA.2212@.TK2MSFTNGP02.phx.gbl...
>> Or, stop SQL Server, make a copy of the files and then restart SQL
>> Server.
> Personally I think it's safer to detach using sp_detach_db. Stopping SQL
> Server will not necessarily correctly initiate the files for attaching to
> another server (at least I've seen plenty of reports of such).
You know, I've heard that it's a "bad idea".
But have actually never heard of it not working, as long as the db was
correctly shut down.
But yeah, if the attach doesn't work, I'd go back to the original server and
try that.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||> But yeah, if the attach doesn't work, I'd go back to the original server
> and try that.
Two more reasons to use an explicit detach:
(a) you only have to take one database offline, instead of ALL databases.
(b) if you in a clustered environment, shutting down SQL Server on the
active node will only initiate a failover, and won't free up the files for
copy because now they are active on the other node.