MS SQL Server - Automatic back up of read-only database fails

Author

Automatic back up of read-only database fails

Dickson Wong

2005-08-21, 3:23 am

I have two sql servers, a production one (SQLA) and a warm backup (SQLB).
SQLA performs log shipping to SQLB and both servers perform nightly backups
of the databases to disk. Backing up to disk on SQLA works but on SQLB the
read-only databases (created by the log shipping) fails. SQLB is able to
back up other databases like the msdb or master.

The error message I am getting is something like this:

BACKUP failed to complete the command BACKUP DATABASE [Dickson] TO DISK =
N'd:\databases\MSSQL
\BACKUP\Dickson\Dick
son_db_200508200400.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Any idea on how to solve this problem?

John Bell

2005-08-21, 7:23 am

Hi

I can backup database that are read only with a similar command, is the
backup file/directory protected?

If you ran the command in QA do you get a better error message?

As a work around you may want to change the job to check DATABASEPROPERTY
('Dickson','IsReadOn
ly') = 0 before issuing the command.

John

"Dickson Wong" <Dickson Wong@discussions.microsoft.com> wrote in message
news:A3A009C3-127E-4BC6-A5DA- 8AF5858DA69A@microso
ft.com...
>I have two sql servers, a production one (SQLA) and a warm backup (SQLB).
> SQLA performs log shipping to SQLB and both servers perform nightly
> backups
> of the databases to disk. Backing up to disk on SQLA works but on SQLB
> the
> read-only databases (created by the log shipping) fails. SQLB is able to
> back up other databases like the msdb or master.
>
> The error message I am getting is something like this:
>
> BACKUP failed to complete the command BACKUP DATABASE [Dickson] TO DISK =
> N'd:\databases\MSSQL
\BACKUP\Dickson\Dick
son_db_200508200400.BAK' WITH
> INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> Any idea on how to solve this problem?
>

Dickson Wong

2005-08-21, 8:23 pm

Thanks for the reply! I didn't run the command in QA but I was able to find
a more detailed log message.:

Error 3036: 'Dickson' is in warm-standby state (set by executing RESTORE
WITH STANDBY) and cannot be backed up until the entire load sequence is
completed.

According to the book "Microsoft SQL Server 2000 Resource Kit", database
backup of the secondary server is possible with log shipping but this
statement is apparently incorrect.

"The Log Shipping process leaves the database on the secondary server in
either a STANDBY or a LOADING status depending upon which option you select
when you configure Log Shipping. Both STANDBY and LOADING are read-only
states, which prevents any writes to the transaction log. Because the backup
process needs to log its operations (backup history), and changes to the
transaction log are disallowed, SQL Server does not allow you to perform
backups performed against a log shipping secondary database. "

Here is the link where I found the solution:
http://www.sql-server-performance.c...e&TOPIC_ID=1813

Dickson

"John Bell" wrote:

> Hi
>
> I can backup database that are read only with a similar command, is the
> backup file/directory protected?
>
> If you ran the command in QA do you get a better error message?
>
> As a work around you may want to change the job to check DATABASEPROPERTY
> ('Dickson','IsReadOn
ly') = 0 before issuing the command.
>
> John
>
> "Dickson Wong" <Dickson Wong@discussions.microsoft.com> wrote in message
> news:A3A009C3-127E-4BC6-A5DA- 8AF5858DA69A@microso
ft.com...
>
>
>

Sponsored Links