473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

:DHRUV
Jul 20 '05 #1
3 3389

"Dhruv" <dm**********@yahoo.com> wrote in message
news:b6*************************@posting.google.co m...
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
Jul 20 '05 #2
[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
Jul 20 '05 #3
THANKS ALOT GUYS, IT WORKED :):)

SORRY FOR NOT REPLYING SOONER

THANK YOU

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
[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.

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Gary L. Burnore | last post by:
REQUEST FOR DISCUSSION (RFD) unmoderated group comp.databases.mysql This is an invitation to discuss the following proposal to create newsgroup comp.databases.mysql. Please note that YOU CANNOT...
0
by: Cara | last post by:
LAST CALL FOR VOTES (of 2) unmoderated group comp.databases.etl Newsgroups line: comp.databases.etl Extraction, transformation, loading issues. Votes must be received by 23:59:59 UTC, 12 Dec...
0
by: Cara Altman | last post by:
REQUEST FOR DISCUSSION (RFD) unmoderated group comp.databases.etl This is a formal Request For Discussion (RFD) for the creation of a world-wide unmoderated Usenet newsgroup...
3
by: Amit | last post by:
Hi when I try to run LIST ACTIVE DATABASES AT DBPARTITIONNUM <partnum> or LIST ACTIVE DATABASES GLOBAL, I always get an error message. Does anyone know why? I'm on v8 fp 5 output : db2 => list...
1
by: com | last post by:
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ......
6
by: Andy | last post by:
Someone posted this official proposal to create comp.databases.postgresql.general again. He wrote his own charter. As far as I know, he did not consult any of the postgresql groups first. There...
1
by: =?Utf-8?B?R2VuY3k=?= | last post by:
Hi, I'm having a problem the coping cd's etc option is missing in Media Center. It used to be available but it has just disappeared! Can anyone help. Thanks
1
oll3i
by: oll3i | last post by:
what are coping constructors and reflexion for ? thank YOU
5
by: Dmitriy Lapko | last post by:
Hallo all Is it possible to change schema of table in DB2 v.8.2 without recreating and coping a table into a new schema? I need it for several purposes, one of them - refactoring of existing...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.