Files, Filegroups, and Transaction Logs

If you want to add a file to an existing filegroup and you are performing regular transaction log backups (as you would with log shipping), you break the restore of the transaction log. This occurs only if the specific path to the file does not exist on the secondary or if the target file already exists. If the file system structure is the same on the secondary, this should not be a problem. There is a two-step fix for the problem scenario:

1. The restore process is now expecting the secondary to have the same physical structure as the primary. Chances are you are seeing this error message:

    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5105: [Microsoft][ODBC SQL  Server Driver][SQL Server]Device activation error. The physical file name  'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\data\lsdb_data2.NDF' may be  incorrect. [Microsoft][ODBC SQL Server Driver][SQL Server]File 'lsdb_data2'  cannot be created. Use WITH MOVE to specify a usable physical file name.  [Microsoft][ODBC SQL Server To solve this problem, restore the next transaction log manually including the  WITH MOVE syntax. Then log shipping should continue without any more problems,  unless you add another file. Here is an example of the RESTORE  WITH MOVE  statement:  RESTORE LOG LOGSHIPDB FROM DISK ='path for the transaction log backup file' WITH MOVE 'Logical name of the new data file'  TO 'physical name of the new data file (where you want the file to be created on  the destination server)', NORECOVERY  -- Or use STANDBY instead of NORECOVERY 

2. If you are using the built-in functionality of SQL Server 2000, you might need to see if the load_all column of the table log_shipping_plan_databases is set to 0 and then check the last_loaded_file column. It should be set to the transaction log file you loaded manually. If it is not, manually update the last_loaded_file column. If load_all is set to 1, all should be fine.