How to Backup Read-Only databases

[tgru]

Hello,

Can anyone tell me how to backup read-only databases? I want to backup
the secondary databases in my log shipping pairs.

Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

tgru (tg**@devdex.com) writes:

Can anyone tell me how to backup read-only databases? I want to backup
the secondary databases in my log shipping pairs.

It may that I don't work with log-shipping that I have to ask this
stupid question: can't you backup it like you would back up any
other database?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[tgru]

Not a stupid question... a very valid question, for the MS folks... I
wish, but when I try to do a backup, it fails with references to the db
being a warm standby database.

It's funny that no-one has come across this... I've asked a few people
and have gotten similar responses from all.

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Phil]

I'm tempted to ask why you would want to do this, since you're
constantly restoring the database anyway. Any backup you take will
quickly be out-of-date, and all the files you need to restore the
database will be in the copy folder on your DR node in any case.

[Erland Sommarskog]

tgru (tg**@devdex.com) writes:

Not a stupid question... a very valid question, for the MS folks... I
wish, but when I try to do a backup, it fails with references to the db
being a warm standby database.

It's funny that no-one has come across this... I've asked a few people
and have gotten similar responses from all.

I broswed a little in Books Online, and could not find anything. But when I
read about standby servers, I made the same reflection as Phil.

I can guess that because of the undo file and that, it may not be possible
to back up a standby server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[tgru]

I agree with you guys in theory, but in our case, our tape backup system
resides at the secondary location (lame, I knw, but I just started
here...) I need to be able to get full backups to the tape backup
system, and copying them from the primary location saturates our T1...

I know the answer now, you cannot backup read-only databases since
backups update the tlog with "backup history", and in read-only mode
that is not possible...

I am going to use DTS to copy the objects and data to a server that I
can do backups from...

Thanks,

TGru

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

tgru (tg**@devdex.com) writes:

I know the answer now, you cannot backup read-only databases since
backups update the tlog with "backup history", and in read-only mode
that is not possible...
No, that is not the case. You can backup a plain read-only database;
I just tried it.

ALTER DATABASE nisse SET READ_ONLY
ALTER DATABSE nisse SET RECOVERY FULL
backup database nisse to disk = 'c:\temp\nisse.back' -- OK

restore database nisse from disk = 'c:\temp\nisse.back'
with standby = 'c:\temp\nisse.standy'

-- This fails:
backup database nisse to disk = 'c:\temp\nisse2.back'

Server: Msg 3036, Level 16, State 1, Line 1
Database 'nisse' is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I guess that's mean, that I would apply the last transaction log. I
suppose that in a log-shipping scenario, that I would have to turn off
log shipping, before I do my backup.
I agree with you guys in theory, but in our case, our tape backup system
resides at the secondary location (lame, I knw, but I just started
here...) I need to be able to get full backups to the tape backup
system, and copying them from the primary location saturates our T1...

Now, is that a kludge or what? But I would still puruse the log shipping
thing - or maybe even better replication. Your idea of using DTS does
not at all sound good to me.

If there is to be any point with this arrangement, the data moved should
only be increments. I know about zero about DTS, but it sounds to me
that you are copying everything everytime. If that backup saturated
the network, guess what your DTS copying will do. And there are big
risks that what you get is not an exact copy of the database. And
it's definitely not a transactional backup.

Here is what I can think of:

1) Get a removable USB 2.0 disk and connect it to the server. Then walk to
to the machine with the tape station. (You should probably backup to
local hard disk, then copy the back up to the USB disk.)
2) Write some routines to turn log shipping off and on, so that standy
by server can be backed up.
3) Investigate transactional replication.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp