Coping Databases

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
:DHRUVFirstly create a new databas
Then backup old databas
Then restore the old database backup file on newly created database (check force restore over existing database|||There's no need to create the database fist, it will be created during the
restore. Just do a backup and restore, when the db name is altered in EM's
restore dialog, EM will also specify new filenames for the database files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mahruti" <anonymous@.discussions.microsoft.com> wrote in message
news:88953F00-4B6F-480F-A3A7-2A9FEB93EA8B@.microsoft.com...
> Firstly create a new database
> Then backup old database
> Then restore the old database backup file on newly created database (check
force restore over existing database)
>|||Correct, I recommend him the long method beacuse I think maybe he can restore to an existing database by accident|||"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0402110919.4a03a3f1@.posting.google.com...
> 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
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|||[posted and mailed, please reply in news]
Dhruv (dmalhotr2001@.yahoo.com) writes:
> 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.
>
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\yourdb.bak'
(Use the file path that is good for your machine.)
Then the RESTORE:
RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
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|||D'you have the same data-file names are you trying to save
them on the same folder.. if so just move the copy of the
datafiles to some other folder (assume D:\SQL2K)
Now attach the files using the following statement (Make
sure that you don't specify the name of the database which
already exists)
Exec SP_Attach_DB 'NewDBName','D:\SQL2K\NewDBFile.Mdb',
'D:\SQL2K\NewDBFile.Ldf'
HTH
Thank you,
Saleem Hakani
>--Original Message--
>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
>.
>|||THANKS ALOT GUYS, IT WORKED :):)
SORRY FOR NOT REPLYING SOONER
THANK YOU
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CED96F4639Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Dhruv (dmalhotr2001@.yahoo.com) writes:
> > 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.
> >
> 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\yourdb.bak'
> (Use the file path that is good for your machine.)
> Then the RESTORE:
> RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
> WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
> MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
> REPLACE
> Note that you don't to create yourdbcopy in advance.
> Again, use the file paths that works on your machine.