September 2, 2005 at 7:16 am
Hello,
I have been experimenting with standby databases under SQl Server 2000. The standby database was successfully created and the logshipping mechanism worked as expected.
However, after I did a structural change on the primary database (I created a new filegroup and datafile and I created a table in the new filegroup) the whole mechanism went wrong.
So here are my questions:
Thanks for your help!
Christophe
September 5, 2005 at 8:00 am
This was removed by the editor as SPAM
September 5, 2005 at 8:20 am
What solution are you using? Builtin log shipping under Maintenance Plans in Enterprise Edition or something else?
I tested log shipping based on the SQL Server 2000 Resource Kit example which I changed slightly and as long as I can remember there was no problem adding a file to a primary database provided that the location (partition, directory structure) existed on standby server's file system. All went smoothly without manual intervention.
What error messages are you getting?
September 5, 2005 at 10:26 pm
In a SQL Server 2000 Log Shipping setup, if a NDF/LDF file is added to the Primary database participating in Log Shipping, the same drive configuration should exist on all secondary servers that participate in Log Shipping with this Primary server, so that the transaction log backup can be restored successfully. If same drive/path do not exist on the secondary server(s), the LOAD job fails and you receive the following error message:
This error message occurs because the new file location is not specified in the restore option and transaction log backup is not programmed to determine a new file location. To correct the failure of the load job on the secondary server, with the first transaction log backup created after the addition of a file for the database from SQL Server Query Analyzer, use the WITH MOVE option and specify the location to which the added file should be created on the standby server.
For example, if northwind_tlog_200101270220.trn is the first transaction log backup created after the addition of a file northwnd_data2 for database Northwind, run the following command to correct the failing load job:
restore log pubs2 from disk ='C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\BACKUP\northwind_tlog_200101270219.TRN'with move 'northwnd_data2' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\data\northwnd_data2.ndf',standby ='c:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\Backup\Northwind.TUF'
After you run the command, the transaction log restore proceeds without the 5105 error message. You may have to make one other correction depending on the load_all setting for the log shipping pair.
If the load_all column for the log shipping pair in the log_shipping_plan_databases table is set to 0, check the last_loaded_file column in the same table and set it to the transaction log backup that was loaded manually. In the preceding example the file is northwind_tlog_200101270219.trn.
If load_all column option is set to 1, you can safely let the load job execute at its scheduled intervals.
September 6, 2005 at 2:48 am
Hello,
Thank you for your answers. I am using Builtin log shipping under Maintenance Plans in Enterprise/Developer Edition.
I do not see any error messages besides that the job to restore the transaction log fails. But what Rajan Samuel said seems logic.
So I will do some more testing with the recommendations given and will post as soon as I have some results!.
Christophe
September 6, 2005 at 6:07 am
Hello again.
I did some more tests and this time I used the same directory structure for the primary and standby database.
The logshipping process is working fine. Structural changes like adding filegroups or datafiles are successfully implemented on the standby database.
So I can say that my problem is solved. Thanks again for the feedback and help I received!.
Christophe
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply