Database backup failure is one of the common issues faced by the DBA. There are many reasons for the backup to fail like Permissions issue, lack of drive space and so on. Recently I came across a question where the backup was failing, but not for the obvious reasons.
What is the issue?
- All the databases on an instance were configured to be backed up using a Maintenance Plan. One of the databases had some issue and it was detached/attached back to the instance.
- The database backups for all the databases were successful except for the one which was attached recently.
- The error message read something like this.
"Cannot open backup device 'D:\backup\mydatabase \mydatabase _backup_201012081958.bak'. Operating system error 3(The system cannot find the path specified.).
What troubleshooting was done?
- Checked if the folder D:\backup existed. It did.
- Checked for the permissions of the service account on the backup path. It was not an issue since the other databases were being backed up to the same path.
- Any error messages in the SQL Server Error Log during that time. No error messages reported except for the one mentioned above.
- Checked the drive for any possible issues. No error messages were available in the Event Viewer.
How the issue got fixed?
A closer look at the error message reveals it all.
Yes, you guessed it right. The database name had trailing spaces in it! While attaching the database through SSMS one extra space got added at the end of the database name. Renaming the database using the below script fixed the issue.
ALTER DATABASE [mydatabase ] MODIFY NAME ='mydatabase'