473,216 Members | 1,204 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,216 software developers and data experts.

SqlServer 2005: How *best* to copy one database to another

Hi;

I thought I would rephrase a question I asked in another post to more
quickly get to the heart of the matter. My apologies for anyone who
is offended by what appears to be a repetition.

My company has two identical web sites. One copy is for our customer,
and one copy is for us to test our code changes on.

We developed a hard to isolate bug in the copy of the web stie for our
customers.

We believe the bug might be data related.

We would like to replace our test database with our production
database to try to reproduce the problem without interrupting our
service to our customers.

The test database and production database have all the same tables and
structures,but the names of the databases are slightly different.

We have tried dropping tables from our test database and importing
tables from our customer database in Management Studio. We got the
data but the identity fields were turned off as identities. We can
manually turn them back on in Management Studio, but we have several
hundred tables.

In the end we just want to the data from our customer db to be in our
test db with the test db name being the same, and have our identity
fields preserved.

What is the easiest way to do this?

Thanks in advance for anyinformation

Steve

May 16 '07 #1
5 34006
Normally a backup or the production database and restore to test works best.
In SQL Server 2005 I would do a copy-only backup.

Here is the syntax for copy-only full backup:

BACKUP DATABASE production_db_name TO <backup_device. WITH COPY_ONLY .

Note that SSMS does not support copy-only backup, you have to run a script.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

May 16 '07 #2
On May 16, 2:22 pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
Normally a backup or the production database and restore to test works best.
In SQL Server 2005 I would do a copy-only backup.

Here is the syntax for copy-only full backup:

BACKUP DATABASE production_db_name TO <backup_device. WITH COPY_ONLY .

Note that SSMS does not support copy-only backup, you have to run a script.
Why is it called a copy only backup?

Is there an option for non-admins?

My test database has a different name ( but identical schema ), once
copy-only backed up, and restored would my admin just then change the
name ( after dropping the old test database? ).
May 17 '07 #3
"Steve" <ti*******@gmail.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.com...
On May 16, 2:22 pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
>Normally a backup or the production database and restore to test works
best.
In SQL Server 2005 I would do a copy-only backup.

Here is the syntax for copy-only full backup:

BACKUP DATABASE production_db_name TO <backup_device. WITH COPY_ONLY .

Note that SSMS does not support copy-only backup, you have to run a
script.

Why is it called a copy only backup?
It is called copy-only backup because because it is independent of the
sequence of the regular SQL Server backups (so it just makes a copy, not
affecting other backups that are scheduled for the same database). When you
perform regular backups (full/differencial/transaction log) there is a
specific sequence that has to be followed when restoring data. Also, the
copy-only backup doesn't truncate the transaction log.

The essential thing to know is that a copy-only backup does not affect your
overall backup and restore procedures for the database.

If all that sounds confusing, you can read more in the SQL Server Books
OnLine about backups.
>
Is there an option for non-admins?
Yes, I believe only members of the sysadmin fixed server role and the
db_owner and db_backupoperator fixed database roles have BACKUP DATABASE
permission by default.

An alternative option is to use the Copy Database Wizard in SSMS (right
click a database, select Tasks, Copy Database...). You can use it to
transfer, move, or copy a database from an SQL Server 2000 or SQL Server
2005 instance to an instance of SQL Server 2005. Just make sure to select
the SMO method (not detach-and-attach) to keep your source database online.

>
My test database has a different name ( but identical schema ), once
copy-only backed up, and restored would my admin just then change the
name ( after dropping the old test database? ).
You can restore over the existing database, that way keeping the name (or
drop first the test database and then restore under the same name). Based on
your security model, your administrator may need to map the database user
and SQL Server login accounts (using the system stored procedure
sp_change_users_login).
HTH,

Plamen Ratchev
http://www.SQLStudio.com

May 17 '07 #4
Plamen Ratchev (Pl****@SQLStudio.com) writes:
An alternative option is to use the Copy Database Wizard in SSMS (right
click a database, select Tasks, Copy Database...). You can use it to
transfer, move, or copy a database from an SQL Server 2000 or SQL Server
2005 instance to an instance of SQL Server 2005. Just make sure to
select the SMO method (not detach-and-attach) to keep your source
database online.
Beware however that the SMO method is very unreliable. I don't know
how many bug reports I have submitted for it during the beta programme of
SQL 2005 and also after release. They have fixed quite a few bugs, but
when I tested what's in SP2, I had reason to file a couple of new bugs,
even if they were for less serious issues.

There are quite a few features that does not work with the SMO method.
If you have a CLR user-defined type, the transfer fails. Certificates
are not copied. To name a few.

Since Steve had problem recreating the issue from the production database
in his test environment, I don't think it is a good idea to use a method
that is known to distort the source. BACKUP/RESTORE is definitely the
way to go.
You can restore over the existing database, that way keeping the name
(or drop first the test database and then restore under the same name).
Based on your security model, your administrator may need to map the
database user and SQL Server login accounts (using the system stored
procedure sp_change_users_login).
To clarify for Steve: this applies if you restore the database on a
different server. If you restore the database on the same server, this
should not be an issue.

Here is a cookbook on how to do it:

1) Run sp_helpdb on the source database. Make note of the logical
device names, those in the second column. I assume here that the
names are srcdb and srcdb_log.
2) Run sp_helpdb on test database, make note of the physical file names.
3) BACKUP DATABASE srcdb TO DISK = 'c:\whatever\srcdb.bak'
4) If needed transfer the file to the test server.
5) RESTORE DATABASE testdb FROM DISK = 'c:\whatever\srcdb.bak'
WITH MOVE 'srcdb' TO '<diskpath>.mdf',
MOVE 'srcdb_log' TO <diskpath>.ldf', REPLACE, STATS = 10
6) Clean up users with sp_change_users_login if needed.

The <diskpathcould be the current location of the test database, or
a new one. It seems like a good idea, to keep the existing test database
and restore copy of a production under a new name.

Once you have learnt how to this, you will find that this is a lot
easier and far less hassle than to run import/export. You get an exact
copy of the database, and not something approxamite.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 17 '07 #5
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...
Plamen Ratchev (Pl****@SQLStudio.com) writes:

Beware however that the SMO method is very unreliable. I don't know
how many bug reports I have submitted for it during the beta programme of
SQL 2005 and also after release. They have fixed quite a few bugs, but
when I tested what's in SP2, I had reason to file a couple of new bugs,
even if they were for less serious issues.

There are quite a few features that does not work with the SMO method.
If you have a CLR user-defined type, the transfer fails. Certificates
are not copied. To name a few.

Since Steve had problem recreating the issue from the production database
in his test environment, I don't think it is a good idea to use a method
that is known to distort the source. BACKUP/RESTORE is definitely the
way to go.
I have myself used the SMO method only a few times for very small databases.
Backup and restore is really what I prefer and using the Copy Database
wizard was more out of curiosity what it offers. Thanks for pointing out the
issues, good to know that.

Plamen Ratchev
http://www.SQLStudio.com
May 17 '07 #6

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

Similar topics

7
by: Bart Torbert | last post by:
Hello, I am starting to examine using SQLServer instead of Oracle. I went through the SQLServer import utility to copy tables from Oracle into SQLServer. I then checked the size of the...
3
by: Dan Sikorsky | last post by:
Can I use SQLServer 2000 with ASP.NET 2.0 instead of SQLServer 2005, and use the .Net 2.0 Membership functionality? I've setup my Login page, controls, etc., and now it's time to use the Web...
2
by: Marc | last post by:
Hi, I hope to find some information on this way, I used to develop databases in Access and want to try visual basic VB.net 2005 Could someone help me how to link 2 datagrid like in the first...
1
by: maflatoun | last post by:
Hi, In SQL 2000 if I wanted to take a complete copy of another running sql database all did was create a new database locally and right-click it and select import and point to another database...
17
by: boa | last post by:
I'm currently planning disk layouts and use for a new version of our database. The current version has all data and indexes in the default filegroup, placed on one big raid-5 array(6 drives) along...
2
by: dgk | last post by:
I'm using the standard provider for membership which is SQLServer Express. After deploying the app to our server, login fails because of an SQL problem. I'm guessing that the problem is that the...
2
by: =?Utf-8?B?SmVmZnJleQ==?= | last post by:
How to configure the IIS, ASPNET userID, Windows or SQL Authentiation, std or integrated security for SQLserver database? The VS.net 2002 web server and SQL Server client are at the same PC, ...
2
by: =?Utf-8?B?SmVmZnJleQ==?= | last post by:
I have some old ASP programs w/ SQLserver 2000 databases. Now I am developing ASP.NET projects using VB 2005 and SQLserver 2005. What are the best procedures to develop and test the ASP.NET...
3
by: Steve | last post by:
Hi; We just migrated to SqlServer 2005. When I import tables from from SqlServer database into another the identity fields get switched off from being identities. How can I prevent that...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...

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.