log shipping backups - 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)

log shipping backups

Author

Topic

creane
Starting Member

11 Posts

Posted - 2003-08-27 : 12:23:40

Hi Ive a quick question and I hope that someone may help. Im hoping to implement log shipping using scripts (sql server 2000 standard edition) my question arises with regard to how log shipping is implemented. Does it involve a one time backup of the production database and a restore to the standby server ONCE, and subsequently backups of the transaction logs are restored against the standby server.? does it require any further restore of a backup of the productin database..? If we also backup the production database, and tlogs outside of the log shipping i.e. theyre not being used with log shipping , just part of the backup strategy, will this affect log shipping, or is just a matter of keeping these backups of db and tlogs separately...
Any help to clear this up greatly appreciated..
thx
Eric

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-27 : 12:28:00

You can not backup the transaction logs outside of log shipping. You can back them up, but you HAVE to apply them to the destination server. You can however backup the database and not apply it to the database server. Log shipping only needs one database backup performed. But I would recommend performing database backups at least once a day. We backup the transaction logs every 15 minutes and then backup the database once per day. All of these files are copied to the destination server, but only the transaction logs are applied.

HTH,

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-10 : 21:24:31

a-ha......

quote:
You can not backup the transaction logs outside of log shipping

quote:
All of these files are copied to the destination server, but only the transaction logs are applied

So how are these shipped files applied when they are auto named?
like a shipped set might be

pubs_tlog_200311102100.TRN
Northwind_tlog_200311102100.TRN

So in the proc that restores the BACKUPS to the destination server you would need these unique names. Right now I can only imagine WSH with a FSO to get the most recent file. Seems awkward.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-11 : 12:04:04

Log shipping information is stored in system tables. So log shipping can read that information to figure out which file it needs. So when a file gets copied to the destination server, some information is stored in the system tables on the destination side. So then the restore job kicks off, it just needs to read the system table to figure out what to do.

But this information is for SQL Server 2000 Ent. Edition. When the manual log shipping is done, you would need to code for the unique names or implement something similar with tables.

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-11 : 12:19:03

Thanks,

Yep I see it now. How to xcopy and script for the unique names?
that is a toughie. I know nr has done it. He used the term "poll"
the directories in an ancient post. Boy I sure hope he replies to this one with some hints. I understand the mechanism of log shipping now and the wizard implimentation in SQL 2000 is no small contraption. I was looking at what is involved last night. The Back office Res. Kit is no longer available or the SQL 7.0 kit.
This is going to take some doing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-11 : 12:35:30

Why not just xcopy the file that was just backed up? You know what the name is of the file because you just used it in the backup command. So why not just xcopy it from the source to the destination?

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-11 : 15:13:03

I agree that this seems logical but the other consideration...
the person who wants to maintain the backup plan as part of a GUI generated Maintenance Plan. So true, I maybe able to do it that way,
maybe not. I looked for the acutual script associated with a maintenance plan_id. I haven't found it yet.

Beth
Starting Member

6 Posts

Posted - 2005-12-07 : 19:16:32

We have SQL Server 2000 Standard Edition. We are trying to simulate log shipping. I am having no luck trying to restore a transaction log backup from the primary server onto the secondary server. I started with a backup of the primary server database and restored it to the secondary server, so theoretically, the databases on both servers are in synch. The transaction log on the primary server is backed up every 15 minutes to a backup device (set to overwrite). If I manually copy the .bak file (for testing purposes) to the secondary server and manually attempt to restore the transaction log from Enterprise Manager, I will get either a LSN out of order error or some message about NORECOVERY (which suggests I haven't restored all of the trans logs, but I have. There's only one). Is it possible that the backup device is screwing me up, because I have it set to overwrite? Should I be using the .TRN files instead of the backup device, as suggested here by someone else.

I understand that log shipping is only supported by Enterprise Edition. However, I was told by a consultant that it is possible to simulate the process with backups/restores of transaction logs from one server to another.

As an aside, has anyone heard of/used the Simple Log Shipping Tool and if so, will this solve our problem? http://www.windowsitpro.com/Article/ArticleID/23231/23231.html

Beth
Starting Member

6 Posts

Posted - 2005-12-08 : 09:12:12

As a followup to my previously posted message. I'm backing up the source database and running a transaction log 15 minutes later. Now, I'm running these commands:

RESTORE DATABASE LogShip
FROM DISK = 'Data_LogShip.bak'
WITH REPLACE

RESTORE LOG LogShip
FROM DISK = 'LogShip_tlog_200512080900.TRN'
WITH RECOVERY

And getting this error:

Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Beth
Starting Member

6 Posts

Posted - 2005-12-08 : 10:55:11

Sorry for the multiple posts.

I was able to successfully restore logs to a secondary server according to the following steps:

NOTE: This is all done from the Enterprise Manager and the test database is called LogShip.

Backup the LogShip database on the PRIMARY server directly from the Backup Device with the OVERWRITE option.
Copy the .bak file to the SECONDARY server.
On the SECONDARY server: From Databases folder, right click to All Tasks...Restore Database... Type in LogShip as the database. Click radio button called Device. Browse to the .bak file. Take default options (Database - Complete). This creates the LogShip database on the SECONDARY server.
Databases are now in synch on the PRIMARY and SECONDARY servers.

Step 1: Add a record to the Ships table in the LogShip database on the PRIMARY server (to test that transactions are getting restored).
Start the database maintainance history job on the PRIMARY server to backup the transaction log of the database, LogShip, thereby creating a TRN file.
Copy the .trn file from the PRIMARY server to the SECONDARY server.
On the SECONDARY server: From the LogShip database, right click to All Tasks...Restore Database...Click radio button called Device. Browse to the .trn file, take the default options, and choose the radio button, "Read backup set information and add to backup history option." (This is a critical step.)
Click OK.
On the SECONDARY server: From the LogShip database, right click to All Tasks...Restore Database... You will see the transaction log added to the backup set. Take default options.
Click OK.
Last Step: Check for added record in the Ships table which is there.
Repeat from Step 1 to Last Step.
New records are showing up on the SECONDARY server, as expected.

As mentioned in this thread, a complication is that the .trn files are uniquely named. How would the SECONDARY server know which .trn file to pick up and restore? If it doesn't pick up the right .trn file, LSNs will get out of sequence and we're SOL.

Secondly, can the steps I outlined above actually be converted to T-SQL so they can be automated?

Thirdly, is it worth it?

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-12-08 : 13:28:10

This adjacent thread has some links that got me started.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58213

I'll help over there if I can, I have a few spare minutes today, but they are rare.

Firstly, you already have a huge chunk of the puzzle figured out.
Secondly, yep
Thirdly, yep because it is such a "low money, just time" solution.
Small fires or water in the IT locker can do a lot of damage.
But it's the following days that really hurt!

"it's definitely useless and maybe harmful".

Beth
Starting Member

6 Posts

Posted - 2005-12-08 : 13:55:29

Thank you. I will redirect my attention to the topic that you mention.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources