|
|
Posted by Erland Sommarskog on 05/17/07 09:51
Plamen Ratchev (Plamen@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 <diskpath> could 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|