Posted by Plamen Ratchev on 01/30/08 15:10
After the database is attached SQL Server Express opens database files with
exclusive access and you cannot do a copy. To release the exclusive access
you have to detach the database. This is done using the sp_detach_db system
stored procedure:
http://msdn2.microsoft.com/en-us/library/ms188031.aspx
Note that in order for detach to work there should be no user connections to
the database. You can obtain exclusive rights to the database when all users
disconnect with something like this:
ALTER DATABASE DatabaseName SET SINGLE_USER;
If you need to force users out of the database immediately, you can use
something like this (note, this will roll back incomplete user
transactions):
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
or
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK AFTER <integer>
[SECONDS];
The second statement will give you an option to roll back after the
specified number of seconds (substitute <integer> with seconds).
More for working with SQL Server Express instances here:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx
More on ALTER DATABASE:
http://msdn2.microsoft.com/en-us/library/ms174269.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
[Back to original message]
|