Restoring Database From External Application

[Tom Loach]

I have the need to restore a database to a user's laptop on a daily
basis. This has been no problem using Enterprise Manager. I found a
stored procedure to run the restore and tried to call it from a visual
basic program. While I can connect to the database, I get an error
telling me "exclusive access could not be obtained because the
database is in use".
Does this mean the only way to run the restore is via Enterprise
Manager?

Thanks,
Tom

[Erland Sommarskog]

Re: Restoring Database From External Application

[posted and mailed, please reply in news]

Tom Loach (tcl4p@virginia .edu) writes:[color=blue]
> I have the need to restore a database to a user's laptop on a daily
> basis. This has been no problem using Enterprise Manager. I found a
> stored procedure to run the restore and tried to call it from a visual
> basic program. While I can connect to the database, I get an error
> telling me "exclusive access could not be obtained because the
> database is in use".
> Does this mean the only way to run the restore is via Enterprise
> Manager?[/color]

No. You get the message because there is an active user in the database.
Judging from the description, it might be the VB program. You should
not specify the database as "Initial catalog" in any connect string,
or issue any USE command for the database.

If you want the restore to work in all situations, you can add a
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE before
the restore and ALTER DATABASE db SET MULTI_USER after.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp