SUMMARY
SQL restore "Failed to create database and attach files"
ISSUE
When restoring a SQL backup, especially a full to an alternate location, the restore fails with:
----- Error Messages ----- Unable to complete restore: Failed to create database and attach files. Unable to complete restore: Failed to create database and attach files. (FILENAME = N'C:/MSSQLSERVER/DATA/SQLPI_data1/data1.MDF'),(FILENAME = N'C:/MSSQLSERVER/DATA/SQLPI_data1/data1_log.LDF') FOR ATTACH ----- End Error Messages -----
RESOLUTION
Make sure the local system administrator account (NT Authority\SYSTEM) has sysadmin privileges within SQL.
More information on SQL requirements are available in our Admin Guide (Requirements and considerations -> Agent prerequisites for Microsoft SQL):
http://www.unitrends.com/documents/rs-ueb-admin-guide/#satori/sql_backup_requirements_and_considerations.htm
CAUSE
The NT AUTHORITY\SYSTEM account must be configured as sysadmin. This account is used to perform SQL backup and recovery jobs. Beginning in SQL Server 2012, SQL does not grant NT AUTHORITY\SYSTEM sysadmin privileges by default. For SQL Server 2012 and later versions, you must manually add NT AUTHORITY\SYSTEM as a system administrator.