How do I copy a database

[Jim Andersen]

I want to take a copy of a database, and mail it to another sql-server, and
then get it up and running.

I normally detach it, and copy the .MDF. But there are users using the
database, so that option is out.

I checked out the copy database wizard, but it can't copy it to the same
sql-server.

I checked out the back-up wizard. It takes a backup ok, but what do I do
with the .BAK file ? I can't rename it to .MDF and attach it on the other
server.

newbie thx from,
/jim

[Greg D. Moore \(Strider\)]

"Jim Andersen" <jimVÆÆ[e-mail]> wrote in message
news:40**********************@news.sunsite.dk...

I want to take a copy of a database, and mail it to another sql-server, and then get it up and running.

I normally detach it, and copy the .MDF. But there are users using the
database, so that option is out.

I checked out the copy database wizard, but it can't copy it to the same
sql-server.

I checked out the back-up wizard. It takes a backup ok, but what do I do
with the .BAK file ? I can't rename it to .MDF and attach it on the other
server.

Restore it.

RESTORE DATABASE FOO from disk='d:\myback.BAK'

Check out the WITH MOVE option also.

And RESTORE FILELISTONLY from disk='d:\myback.BAK' may be useful.

newbie thx from,
/jim

[Erland Sommarskog]

Jim Andersen (jimVÆÆ[e-mail]) writes:

I checked out the back-up wizard. It takes a backup ok, but what do I do
with the .BAK file ? I can't rename it to .MDF and attach it on the other
server.

First sp_helpdb on the database, take note of the logical filenames.
(You can also retrieve this with RESTORE FILELISTONLY.)

Then:

RESTORE DATABASE some_db FROM disk = 'C:\dump-in-the-mail.bak'
WITH MOVE 'some_db' TO 'C:\MSSQL\Data\some_db.mdf',
MOVE 'some_db_log' TO 'D:\MSSQL\Logs\some_db.ldf',
REPLACE

Replace all names and locations to what applies to your case.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp