Change State of Database

[ashab02]

Hello

How do I change the state of a database to 'STANDBY'

If the above doesnt make sense how do I chnage the state of a database

If somebody has the Query Analyser code then that will be great

Shab

 

[SQLBill]

You can't set an operational database to STANDBY. STANDBY is a RESTORE DATABASE option. It means the database is partially restored and more logs can be restored to it, but it is also usable (readonly) to users.

You can change a database to READONLY by code. But that is not the same as STANDBY.

You can find the directions for doing both in the BOL (Microsoft SQL Server Books OnLine - help files that come with SQL Server).

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

perhaps you mean offline?

USE MASTER
GO

ALTER DATABASE pubs SET OFFLINE



ALTER DATABASE pubs SET ONLINE

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ashab02]

Hello

The prblem i'm having is that I am trying to restore a differential backup to an online database and i'm getting an error.

Basically I just want to know how to restore a differential backup to an online database.

Can anyone help???

 

[SQLBill]

Can't do that. Once a database is recovered you can't restore anymore backups to it.

-SQLBill

Posting advice: FAQ481-4875

 

[ashab02]

Hello

Please note that there has been no changes to the database since the restore

I'm pretty sure it can be done

Do you have any other ideas?

Shab

 

[SQLBill]

From the BOL:

RECOVERY

Instructs the restore operation to roll back any uncommitted transactions. After the recovery process, the database is ready for use.

If subsequent RESTORE operations (RESTORE LOG, or RESTORE DATABASE from differential) are planned, NORECOVERY or STANDBY should be specified instead.

If you restore the database WITH RECOVERY, that is it...you cannot restore any more files. To be able to restore additional backups, the database must have been restored WITH NORECOVERY or to STANDBY.

-SQLBill


Posting advice: FAQ481-4875