Log shipping in SQL 2000 Standard Edition

[WinniePooh]

I have configured log shipping in our SQL 2000 Standard Edition by following the process detailed in this link:

http://www.sql-server-performance.com/sql_server_log_shipping.asp

Each time a backup runs (database or transaction log) the backup device is overwritten. If the standby server becomes out of sync with live we cannot retrieve old transaction logs and bring them back into sync.

Is there a way of changing the backup/restore procedures detailed in the link to create unique backup filenames and apply these unique backups to a standby server?

Thanks.

 

[paul172]

I had this same problem with this article. I created hourly transaction log backups with the same steps, and called each one, LogBackup 1AM.bak. I never really got it working 100% though, so don't take that as gospel.

In the end, after many weeks of trying, I gave up on using Standard to carry out log shipping. I'm in the process of asking the people with money to upgrade to Enterprise.

 

[JayKusch]

As the docs say, Log Shipping set up in version of SQL Server other than Enterprise and Developer are unsupported!

However, the Microsoft SQL Server 2000 Resource Kit comes with a set of unsupported stored procedures for log shipping in the other SQL Server 2000 editions .

Thanks

J. Kusch

 

[mrdenny]

As Jay said log shipping in Standard edition is unsupported by Microsoft. Writting your own procs to get log shipping working shouldn't be all that difficult.

Microsoft's Log shipping isn't the save all solution. I have heard all sorts of stories about it not working correctly.

Personnaly I'm not fond of the log shipping procedures provided with that site. Here is the basic setup that I like to use.

Manually do a full backup of your database and restore it to the standby server with the standby flag set.

On the production server setup a job. Step one of the job will backup the log to a file on the hard drive of the standby server. (I've tried backing up to the local disk, and backing up to the standby server just works better, so bear with me).

backup log {database_name} to disk='\\server2\Backup\TheFile.bak'

That code will to the trick nicly. We will just keep appending to that file as needed.
The second step of the job should be a Command Executable. You can going to run osql and have it log into the standby server and start the job that will do the restore.

osql -S server2 -E -d msdb -Q "sp_start_job @job_name='{Restore Job Name}'"

On the standby server create a job named what ever you used in step to of the job on the production server.
This job will rename the transcation log backup file so that it can work with it, while the production server can still acess the file as needed.
The next thing the job will do is read the file list info of the backup job and see how many backups are in the file, so that it knows how many transaction log restores need to be done. The basic code is something like this.

xp_CMDShell 'rename d:\backup\TheFile.bak d:\backup\TheFile2.bak'

declare @BackupCount int
declare @i int
restore filelistonly from disk='d:\backup\TheFile2.bak'
set @BackupCount = @@ROWCOUNT  /*We now know how many backups there are.*/

set @i = 1
/*This code will work it's way through the backup file in the event that there is more than one backup in there.*/
while @i <> @BackupCount+1
BEGIN
    restore log {database_name} from disk='d:\backup\TheFile2.bak' WITH FILE = @i, STANDBY=d:\Backup\{Database_name}.StandBy
    set @i = @i + 1
END

The reason that I like to do it this way is so that in the event that it takes longer to restore the file than it does to wait for the next backup the backups can still run with out effecting the restores, and the restores don't effect the backups. In plain english.
Backup fires at 00:00. It starts the restore at 00:03.
Backup fires at 00:05. Restore is still running.
Restore finishes at 00:07
Backup fires at 00:10. It starts the restore at 00:13.
There are now 2 backups to process.
With the restores running independently of the backups you still get your backups every 5 minutes no matter if the restore is running still or not. With the procedures on the page you listed you will not get another backup until the restore is complete.

I hope this helps.

Denny

--Anything is possible. All it takes is a little research. (Me)