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

Steve

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

Plamen Ratchev

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

Steve

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

Plamen Ratchev

"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

Erland Sommarskog

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

Plamen Ratchev

"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