You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q286280
SUMMARY
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:
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.
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.
MORE INFORMATION
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:
RESTORE LOG NORTHWIND 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				
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 northwindADD 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 1The 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 1Backup 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:
ALTER DATABASE NorthwindADD FILE ( NAME = northwind_data1, FILENAME ='E:\MSSQL7\DATA\northwnd_data1.ndf', SIZE = 5MB, MAXSIZE = 15, FILEGROWTH = 10%)GO				
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:
Processed 1 pages for database 'northwind', file 'Northwind_log' on file 1.
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(The system cannot find
the path specified.) while attempting to open or create the physical file
'E:\Program Files\Microsoft SQL Server\MSSQL\DATA\nwnd_d2.ndf'.
Server: Msg 5183, Level 16, State 1, Line 1
File 'northwind_data2' cannot be created. Use WITH MOVE to specify a usable physical file name.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
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:
[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\northwnd_data2.NDF' may be incorrect.[Microsoft][ODBC SQL Server Driver][SQL Server]File 'northwnd_data2' cannot be created. Use WITH MOVE to specify a usable physical file name.[Microsoft][ODBC SQL Server
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.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
281122 INF: Restore File and Filegroup Backups in SQL Server
Properties

Article ID: 286280 - Last Review: 02/22/2007 22:31:04 - Revision: 4.3

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • kbinfo KB286280
Feedback