Post reply

Log Shipping in SQL Server 2000 Standard Edition

  • September 15, 2005 at 3:35 am

    #111771

    Hi,

    please could somebody advise me on implementing log shipping in SQL Server 2000 standard edition. I have seen several examples on how to set this up, but they all seem to rely on the process using fixed names for the transaction log files.

    I have a maintenance job running which creates a transaction log file every 15 minutes with a filename in the format Database_tlog_yyyymmddhhmm.TRN.

    I was wondering if it was possible to extend the job so that the file is moved to a backup server and applied to the database, but to do that I would need to identify the filename created in the previous step from within the Job. Is it possible to do this, or is there perhaps a database table I could query that would tell me the name of the transaction file just created?

    Thanks

    David

    If it ain't broke, don't fix it...

  • September 15, 2005 at 6:30 am

    #590091

    SELECT     TOP 1 *

    FROM         MSDB.DBO.backupmediafamily

    ORDER BY media_set_id DESC

    that will give you the last backup that was carried out, i'm sure you can change it a bit to work out just the filename, or failing that have a look at the other backup tables in the db, it might already be there (too busy to check).

    Hope this helps

  • September 15, 2005 at 8:09 am

    #590151

    Hi Mike,

    thanks for that information. If I feed the file-path into xp_cmdshell to xcopy the file onto the backup server I can have the server restore it to the database there.

    Now i wonder how you execute a job step on another server? I think you have to use RPC...

    David

    If it ain't broke, don't fix it...

  • September 16, 2005 at 1:55 am

    #590449

    The better solution in my opinion is to create two procs - one on primary server and second on the standby. The first one performs backup and as a last step calls the proc on standby with a backup file name as a parameter. The proc on standby in turn performs the restore. Thus you avoid potential problems with backup names. You must set up the standby server as a linked server on primary in order to call remote stored procedure. As to file location you can either create a share that is accessible by both servers or you can copy backup to standby as you suggested (as a step in the primary proc).

  • September 16, 2005 at 2:24 am

    #590455

    Hi Martin,

    Thanks for your suggestion. I was thinking about writing the filenames to a table on the backup server, and having the backup server process the contents of that table in order at scheduled intervals. Even better, I had thought, would be to somehow recognise the files in a directory and get SQL Server to process them in order. It must be possible to do this, I am sure I have seen requests for help with loading data files that require this solution.

    I realise however that this falls into the category of 'fire and forget' which has been discussed recently in this forum, and that I would be better to follow your suggestion and keep control of the entire process in one place.

    I have found that creating a share on the backup server with appropriate permissions for the SQL Server/SQL Agent account, and mapping a drive to it from the primary server gives xp_cmdshell the appropriate filepaths to perform the xcopy.

    I have linked the two servers, so I am going to try creating an appropriate procedure for the backup server (in a different database , master maybe) and pass the filename as a parameter

    David

    If it ain't broke, don't fix it...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply