Stand-by Server help - 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 2000 Forums

SQL Server Administration (2000)

Stand-by Server help

Author

Topic

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-05-26 : 13:43:59

hi all, I try to setup a stand-by server, I make a initial full backup of my DB using :

BACKUP DATABASE [DBTEST1] TO DISK = N'\\<remote server>\logship\test1.bak' WITH INIT , NOUNLOAD , NAME = N'DBTEST1 backup', SKIP , STATS = 10, NOFORMAT

** I restore that backup on my stand-by server

use master
RESTORE DATABASE DBTEST1
FROM disk = 'h:\logship\test1.bak'
WITH STANDBY = 'c:\undo.ldf'

** I program periodical log backups

BACKUP LOG [DBTEST1] TO DISK = N'\\<remoteserver>\logship\testlog.bak' WITH INIT , NOUNLOAD , NAME = N'AUDITORIA backup', SKIP , STATS = 10, NOFORMAT , NO_TRUNCATE

** I restore the log and wait for more logs to apply.

RESTORE LOG DBTES1
FROM disk = 'h:\logship\testlog.bak'
WITH STANDBY = 'c:\undo.ldf'
GO

All see to be ok, because I can apply all the periodical logs, but here is my problem.

like this is a production server I have to make backup to a tape with this instruction:

BACKUP DATABASE [DBTEST1] TO [SDLT TAPE] WITH NOINIT , NOUNLOAD , NAME = N'DBTEST1 backup FULL', SKIP , STATS = 10, DESCRIPTION = N'DBTEST1 Full', NOFORMAT

and I make a disk backup on a remote server too:

BACKUP DATABASE [DBTEST1] TO DISK = N'\\<remoteserver>\logship\dbtes1\fullbk.bak' WITH INIT , NOUNLOAD , NAME = N'DBTEST1 backup', SKIP , STATS = 10, NOFORMAT

the question is, at some moment after the full backup (to tape and disk) i can't reapply more transaction logs:

The log in this backup set begins at LSN 32583000000018400001, which is too late to apply to the database. An earlier log backup that includes LSN 32573000000004300001 can be restored. [SQLSTATE 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

so, if I apply to the stand-by the full disk backup (made with the tape backup) to my DB and after i apply a transaction log maded after the backup I get the same error. But if I make a new disk backup, restore at the stand-by server, and start a new log restore process it will work.

So, I need to know where my problem are!!!

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 13:49:08

You are missing WITH NORECOVERY on your RESTORE commands.

It's fine to make a full backup to tape. That will not cause any problems for your standby server. It's only if you make transaction log backups that will be a problem. All transaction log backups need to be applied to the standby server.

Also, why are you backing up directly to tape? You should be copying the file from your first BACKUP command that you posted to tape. Do not backup directly to tape. It is not much easier and faster to just copy the file to tape.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 13:58:05

The full backup to tape is not causing this problem. According to the error, you haven't applied the transaction logs in the correct order. Every single transaction log that is backed up on the source needs to be applied on the standby server.

Tara

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-05-26 : 14:05:11

Tara thanks for your help, but if I use NORECOVEY i can't use STANDBY, that will be correct????
and about backup to tape, I use that way to avoid use a anoter program to restore my backups, or exist a way to put the diskbackups to a tape ??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 14:11:25

Your tape backup software will allow you to pull files from disk to tape. Your tape backup software is probably already pulling over files to tape, so have it pull that file too. Backing up a SQL database directly to tape is very slow. It also sometimes doesn't work, which is why most of us just copy the .BAK and .TRN files to tape since backups are so important.

Yes you can still use STANDBY with NORECOVERY. According to BOL, you only need to specify one of them. I put both of them in my script though.

So to fix your problem, you need to figure out why the transaction logs aren't being applied in the correct order. It's possible that you've got a maintenance plan that is causing them to get out of sync.

Tara

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-05-26 : 14:21:38

ok, thanks for the backup tip!.
so, just to ckeck, if I make a full backup and apply to my Stand-by using

RESTORE DATABASE DBTEST1
FROM disk = 'h:\logship\test1.bak'
WITH NORECOVERY

And apply the periodical logs using:

RESTORE LOG DBTES1
FROM disk = 'h:\logship\testlog.bak'
WITH STANDBY = 'c:\undo.ldf'
GO

thats is correct???

so a question I guest a little dummy, but i have, If ater are appling logs on the standby server, I make a backup on my On-line server, and make a inmediatly log backup, with I have to apply to my stand-by ???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 14:26:32

Here is what it would look like:

Full backup on Source
Restore backup on Standby using:

RESTORE DATABASE DBTEST1
FROM disk = 'h:\logship\test1.bak'
WITH NORECOVERY, STANDBY = 'H:\logship\undo_DBTEST1.DAT'

Then backup log on Source
Then restore log on Standby

RESTORE LOG DBTEST1
FROM disk = 'h:\logship\testlog1.trn'
WITH NORECOVERY, STANDBY = 'H:\logship\undo_DBTEST1.DAT'

Then let's say you perform a full backup then a transaction log as you mentioned. You only need to apply the transaction log. You never again have to apply the full backup, although you could.

RESTORE LOG DBTEST1
FROM disk = 'h:\logship\testlog2.trn'
WITH NORECOVERY, STANDBY = 'H:\logship\undo_DBTEST1.DAT'

So for EVERY transaction log backup that you make, you apply it to the standby server. As long as you do the restores in the order of the backups, you'll never get that error.

I changed your standby file name and location to use the standard. The standard is that the file is locate din the same directory as the transaction log files and it's name is undo_DBName.DAT.

I also changed the extension of the transaction logs. The standard is TRN.

Standardizing things just makes it easier to find and know what the file is.

Tara

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-05-26 : 14:35:12

Hey Tara Thank you VERY MUCH!!!!

Regards.
Gerardo G.

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-05-26 : 14:44:48

Hey If I make a full DB backup and I don't make a transaction log until a few hours later, I just apply the transaction log?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 14:46:48

Yes. We never apply the full backup. But we do copy the full backup to the standby server.

Tara

MuadDBA

628 Posts

Posted - 2004-05-26 : 16:02:13

Huh? Who? How? I thought a full backup captured all committed transactions since the last log backup, and it cleared out the transaction log as a result...am I thinking of an old version of SQL Server or something?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 16:16:14

From BOL:

During a full database or differential backup, Microsoft® SQL Server™ backs up enough of the transaction log to produce a consistent database for when the database is restored.

For BACKUP LOG:

The log is backed up from the last successfully executed LOG backup to the current end of the log. Once the log is backed up, the space may be truncated when no longer required by replication or active transactions.

I take it you don't have a standby server that you log ship to?

Tara

MuadDBA

628 Posts

Posted - 2004-05-27 : 09:48:40

Actually I do, that's the funny part. I was always having problems with my LSN's after I did the nightly backup on the production server, so now as part of the log shipping I restore the nightly backup first, then all of the following tlogs and I haven't had a problem.

I'll have to try setting it up again. This is the cool part about coming here regularly, I find out stuff about processes I have been using for years that I *thought* were working flawlessly.

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-05-28 : 10:52:27

Hi, doing this:

RESTORE Database SPE
FROM disk = 'H:\logship\SPE\fullbk.bak'
WITH NORECOVERY,STANDBY = 'H:\logship\SPE\undo_spe.df'

I get this error

Server: Msg 3031, Level 16, State 1, Line 1
Option 'norecovery' conflicts with option(s) 'standby'. Remove the conflicting option and reissue the statement.

I missing something? a special option most by defined at the db or to the backup ???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-28 : 12:25:18

I take it back then. Just specify STANDBY if you want the destination database to be read-only or NORECOVERY if you don't.

Tara

gercr
Yak Posting Veteran

53 Posts

Posted - 2004-05-28 : 12:58:08

OK, THANKS!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources