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\)]

Re: How do I copy a database


"Jim Andersen" <jimVÆÆK@office consult.dk> wrote in message
news:40a8baa5$0 $3045$14726298@ news.sunsite.dk ...[color=blue]
> I want to take a copy of a database, and mail it to another sql-server,[/color]
and[color=blue]
> 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.
>[/color]

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.

[color=blue]
> newbie thx from,
> /jim
>
>[/color]

[Erland Sommarskog]

Re: How do I copy a database

Jim Andersen (jimVÆÆK@office consult.dk) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp