Article ID: 286280 - View products that this article applies to.
This article was previously published under Q286280
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.
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.
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.0Consider 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.
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 2005With 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.
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.
To work around this 5123 error message, follow these instructions:
SQL Server 2000 Log Shipping ScenarioIn 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.
[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
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.
REFERENCESFor additional information, click the article number below to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/281122/EN-US/ )INF: Restore File and Filegroup Backups in SQL Server
Article ID: 286280 - Last Review: February 22, 2007 - Revision: 4.3
Contact us for more help
Connect with Answer Desk for expert help.