Point in time Restore - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Point in time Restore

Author

Topic

dbaman
Starting Member

46 Posts

Posted - 2007-11-07 : 18:32:51

I have a SQLServer 2005 database running in Windows 2003 Advanced Server environment. I want to restore from the backup to 2 days back point in time. I am using the Microsoft SQLServer Managment Studio. After I pick the file and specify the time I keep getting the error:

System.Data.SqlClient.SqlError: RESTORE cannot process database 'DBNAME' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)

I did a server reboot to clear off any hanging session.Stil I am getting the message. Please suggest.

Thanks

R

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 18:38:14

You must be accessing the database at the same time you are attempting the restore. Disconnect from your session, reconnect, then without clicking on any databases, right click on Databases "folder" and select restore database.

I prefer to use the RESTORE command for more control though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

dbaman
Starting Member

46 Posts

Posted - 2007-11-07 : 18:47:42

Thanks for the reply.

I am not accessing the database. After launching the Microsoft SQLServer Managment Studio I am clicking on the Object Explorer pane --->Database--System Database (Right Click and pick the option Restore Database)

R

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 18:52:52

Try restoring via a query then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-07 : 21:24:31

Run the following code in a query window to take the database offline to prevent anyone from connecting to it before you start the restore.


use master
alter database MyDatabase set offline with rollback immediate

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 00:47:04

MVJ: does the subsequent restore bring it back online, or is a subsequent ALTER needed for that?

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-08 : 09:35:23

quote:
Originally posted by Kristen

MVJ: does the subsequent restore bring it back online, or is a subsequent ALTER needed for that?

Kristen

The restore will bring it online.

It is also a good way to get single user access to a database:


use master
alter database MyDatabase set offline with rollback immediate
alter database MyDatabase set online, single_user with rollback immediate
use MyDatabase

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 13:49:10

"The restore will bring it online."

Thanks

"use master
alter database MyDatabase set offline with rollback immediate
alter database MyDatabase set online, single_user with rollback immediate
use MyDatabase"

I normally just do

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

and given that I am "in" the database I effective grab the single connection (I presume!)

Is there some (practical) difference between the two methods?

Thanks

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources