[posted and mailed, please reply in news]
Dhruv (dm**********@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,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp