Coping Databases

[Dhruv]

Hi,

My questions is we want to make a copy of a database onto the same
server while preserving the diagrams, stored procs, etc.

We stopped the SQL service and made a copy of the data and log files.

We attempted to ATTACH the file copies (after re-naming them), but the
embedded file information tells SQL that the database already exists.

Our database is a piece of junk, but we must use it. If we don't want
to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
errors during the transfer), how can we make an exact copy of the
Database onto the same server while giving the "new" database a
different name?

Thanks

:DHRUV

[Simon Hayes]

Re: Coping Databases


"Dhruv" <dmalhotr2001@y ahoo.com> wrote in message
news:b6d0b0b.04 02110919.4a03a3 f1@posting.goog le.com...[color=blue]
> Hi,
>
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
>
> We stopped the SQL service and made a copy of the data and log files.
>
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.
>
> Our database is a piece of junk, but we must use it. If we don't want
> to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
> errors during the transfer), how can we make an exact copy of the
> Database onto the same server while giving the "new" database a
> different name?
>
> Thanks
>
> :DHRUV[/color]

Probably the quickest and easiest way is to back up the source database,
then restore it with a different name. You can do this from Enterprise
Manager, or using RESTORE. See "Copying Databases" in Books Online.

Simon

[Erland Sommarskog]

Re: Coping Databases

[posted and mailed, please reply in news]

Dhruv (dmalhotr2001@y ahoo.com) writes:[color=blue]
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
>
> We stopped the SQL service and made a copy of the data and log files.
>
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.
>[/color]

I use sp_attach_db rarely, but I fail to see see why it would work.
Then again, I've been wrong before.

Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
a bit complex.

First use sp_helpdb to see what the logical names of your data files are;
that's the first column. If your database is named yourdb, then the logical
names are typilcally yourdb and yourdb_log.

Then the backup:

BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\you rdb.bak'

(Use the file path that is good for your machine.)

Then the RESTORE:

RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\you rdb.bak'
WITH MOVE 'yourdb' TO 'C:\databasefil es\yourdbcopy.m df',
MOVE 'yourdb_log' TO 'D:\databaselog s\yourdbcopy.ld f',
REPLACE

Note that you don't to create yourdbcopy in advance.

Again, use the file paths that works on your machine.


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

[Dhruv]

Re: Coping Databases

THANKS ALOT GUYS, IT WORKED :):)

SORRY FOR NOT REPLYING SOONER

THANK YOU

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns948CED 96F4639Yazorman @127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Dhruv (dmalhotr2001@y ahoo.com) writes:[color=green]
> > My questions is we want to make a copy of a database onto the same
> > server while preserving the diagrams, stored procs, etc.
> >
> > We stopped the SQL service and made a copy of the data and log files.
> >
> > We attempted to ATTACH the file copies (after re-naming them), but the
> > embedded file information tells SQL that the database already exists.
> >[/color]
>
> I use sp_attach_db rarely, but I fail to see see why it would work.
> Then again, I've been wrong before.
>
> Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
> is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
> a bit complex.
>
> First use sp_helpdb to see what the logical names of your data files are;
> that's the first column. If your database is named yourdb, then the logical
> names are typilcally yourdb and yourdb_log.
>
> Then the backup:
>
> BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\you rdb.bak'
>
> (Use the file path that is good for your machine.)
>
> Then the RESTORE:
>
> RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\you rdb.bak'
> WITH MOVE 'yourdb' TO 'C:\databasefil es\yourdbcopy.m df',
> MOVE 'yourdb_log' TO 'D:\databaselog s\yourdbcopy.ld f',
> REPLACE
>
> Note that you don't to create yourdbcopy in advance.
>
> Again, use the file paths that works on your machine.[/color]