You are currently offline, waiting for your internet to reconnect

Error message when you try to restore a database by using SQL Server Management Studio in SQL Server 2005 after you use the Backup tool: "Restore failed for Server '<ServerName>' (Microsoft.SqlServer.Smo)"

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.

SYMPTOMS
Consider the following scenario. The SQL Writer service is running on a computer that has Microsoft SQL Server 2005 installed. You use SQL Server Management Studio to back up a database. Additionally, you use the Backup tool (NTBackup.exe) to back up the database files. Then, you try to restore the database from the backup set by using SQL Server Management Studio. In this scenario, you receive an error message that resembles the following:
Restore failed for Server '<ServerName>'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device '<GUID of the device>'. Operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)
If you examine the start date and the finish date of the backup set, you find that the dates are updated to the date that you used the Backup tool.
CAUSE
This problem occurs because the Backup tool uses the SQL Writer service to back up SQL Server 2005 databases. The Backup tool triggers the SQL Writer service to create a snapshot. After the SQL Writer service creates the snapshot, the Backup tool determines whether the database files are on the exclude list. The database files include .ldf files and .mdf files. If the database files are on the exclude list, the Backup tool deletes the database files from the snapshot. However, the Backup tool adds entries in the backupset table in the msdb database.
RESOLUTION
To resolve this problem, stop the SQL Writer service when you use the Backup tool if you use both the Backup tool and the SQL Server 2005 backup functionality on the computer. Do not use the Backup tool to back up the database files. Instead, use the built-in functionality in SQL Server 2005 to back up the database.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
If you examine the SQL Server error log file, you find messages that resemble the following message for the user databases and for the system databases:
Date Time SPID I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Date Time SPID I/O was resumed on database model. No user action is required.

Date Time SPID Backup Database backed up. Database: master, creation date(time): Date(Time), pages dumped: 1, first LSN: 300:248:52, last LSN: 301:120:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'<GUID of the device>'}). This is an informational message only. No user action is required.
Properties

Article ID: 937683 - Last Review: 11/20/2007 17:47:14 - Revision: 1.3

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems, Microsoft SQL Server 2005 Workgroup Edition

  • kbtshoot kbsql2005engine kbexpertiseadvanced kbprb KB937683
Feedback
;did=1&t=">age in languagesListForLargeScreens track by $index -->
España - Español
Paraguay - Español
Venezuela - Español
0&did=1&t=">amp;did=1&t=">&did=1&t=">; var Route = "76500"; var Ctrl = ""; document.write(" d')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> >