Description of the effect to database recovery after you add or remove database files

Adding database files between transaction log dumps in SQL Server 7.0 renders transaction log dumps created after the file addition unusable.

If you are trying to recover a database by using a full backup and a sequence of transaction log backups, a transaction log backup created after the addition or deletion of a database file cannot be used in the recovery process. An attempt to restore the first transaction log backup created after the addition of a file fails with the following error message: Therefore, you must create a full backup of the database after adding or deleting a database file to the database to start a new sequence of backups.

You can see an extension of the preceding problem in a Log Shipping setup. When you set up log shipping between two SQL Server 7.0 servers, the transaction log dumps are continuously being transferred and restored on the standby server. If, for any reason, a log file is added to the production database, the transaction log backups cannot be restored on the standby server without resynchronizing the log shipping pair.

For example, you might add a database file because of a lack of disk space on a particular drive. After you add a log file, log shipping cannot continue with the transaction log restores. Subsequent transaction log restores on the standby server fail with the error message stated in the "Summary" section.

With SQL Server 2000 or SQL Server 2005, you can use a WITH MOVE command while restoring the transaction log backup that was created after the addition of the database file. The restore operation in SQL Server 2000 or in SQL Server 2005 can handle the addition of database files. For example, you can restore the first transaction log backup that is created after adding a database file by using this code: The result is a database with the added file. The restore operation creates a new file and writes data over the new file. You can then use the RESTORE command without using the MOVE option. If a database file is deleted in a SQL Server 2000 or SQL Server 2005 database, the restore operation will continue without any modification and SQL Server internally recognizes the removal of files and adjusts the database accordingly.

The sections that follow provide a sequence of steps that describe the behavior in SQL Server 7.0 and the improvements in SQL Server 2000 and in SQL Server 2005.

SQL Server 7.0

Consider a setup where a SQL Server 7.0 database is used in production and the database is being backed up continuously. When trying to restore from the backups, you must restore the last full backup (by using WITH NORECOVERY) followed by n-1 transaction log backups (by using WITH NORECOVEYR) and the last transaction log backup (by using WITH RECOVERY).

The following steps describe a sequence leading to the addition of a database file between transaction log dumps on the production database and the resulting errors that occur when you use the backups to perform a restore are described.

NOTE: The following steps were performed on a database in use.

1. Backup a SQL Server 7.0 database.
2. Create a transaction log backup on the the tran_log1.trn file.
3. Add a data file for the database by using:

   ALTER DATABASE northwind
   ADD FILE 
   (  NAME = northwind_data1,
      FILENAME ='E:\MSSQL7\DATA\northwnd_data1.ndf',
      SIZE = 5MB,
      MAXSIZE = 15,
      FILEGROWTH = 10%
   )
   GO
   					

4. Backup the transaction log to the tran_log2.trn file.

When a database failure occurs or if you want to restore from a backup to recover the database by using the backup sequence created previously, follow these steps:

1. Restore a SQL Server 7.0 database by using: WITH NORECOVERY
   Restore Successful
   
   
   Processed 360 pages for database 'northwind', file 'Northwind' on file 1. Processed 1 pages for database 'northwind', file 'Northwind_log' on file 1. Backup or restore operation successfully processed 361 pages in 0.810 seconds (3.642 MB/sec)
2. Restore the transaction log backup by using WITH NORECOVERY from tran_log1.trn:
   Restore Successful
   
   Processed 1 pages for database 'northwind', file 'Northwind_log' on file 1. Backup or restore operation successfully processed 1 pages in 0.004 seconds (1.664 MB/sec).
3. Restore the transaction log by using WITH NORECOVERY from tran_log2.trn: The restore fails with this error message:
   Server: Msg 3155, Level 16, State 1, Line 1 The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

As the error message indicates, the transaction log restores cannot continue from the preceding transaction log backup (tran_log2.trn) forward. This behavior is by design and SQL Server 7.0 does not allow the addition or deletion of files between backup sequences.

To avoid the preceding errors, create a full backup after you add or delete one or more database files, which starts a new backup sequence. If the backup sequence created previously is the only recovery option available, restoring tran_log1.trn by using the WITH RECOVERY option recovers a database to the state just before the database files were added or removed.

SQL Server 2000 and SQL Server 2005

With SQL Server 2000 and SQL Server 2005, the addition or deletion of files is handled seamlessly without the need to start a new backup sequence. After adding a file to the database in the middle of a transaction log backup sequence, the next transaction log backup should be applied manually by using the WITH MOVE option.

Consider a scenario similar to the one used for the preceding SQL Server 7.0 example. After a file is added to the production database by using: If a transaction log backup is performed on the database after the file addition, trying to restore the first transaction log backup (tran_log2.trn) after the file addition, generates this error message: The error message occurs because the RESTORE command has not been provided with the option to create a new file (corresponding to the file added on the production database) and write data to the file.

To work around this 5123 error message, follow these instructions:

1. Manually restore the first transaction log backup created after the file addition (from SQL Server Query Analyzer or SQL Server Enterprise Manager) by using this code:

   RESTORE LOG NORTHWIND FROM 
   DISK='e:\filemgt\nwind_log2.trn' 
   WITH MOVE 'northwind_data1'  
   TO 'c:\Program Files\Microsoft SQL server\MSSQL$SQL2K1\Data\northwnd_data1.ndf',
   NORECOVERY
   					

   You have now created a new database file just as it exists on the production server database on which the transaction log backups are being created.

After you execute the command in step 1, you can enable further transaction log restores so that they can continue without using the MOVE option.

SQL Server 2000 Log Shipping Scenario

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: 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.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: