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