We have a job which backs up all ONLINE databases nightly, but for some reason it didn't back up msdb. So the issue is at that time msdb was in some status was other then 0. So my question is : is it possible to back up a database if its status is not online (either RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY)?
Sql-server – Is it possible to backup a database that is not ONLINE
backupsql server
Related Question
- SQL Server Query for Backup Information (Successful and Failed)
- Sql-server – SQL Server 2005 Restore Backup Status Recovery Pending
- Databases in Suspect State After Enabling TDE in SQL Server
- Sql-server – DB STARTUP command active for an online database
- SQL Server – Is Simple Recovery Mode with Mirroring Possible?
- SQL Server – BACKUP WITH CHECKSUM vs. DBCC CHECKDB Issues
- SAP Oracle Full Online Backup Failing
- Sql-server – Why did full backup file size grow after data movement for Availability Group replica was suspended
Best Answer
It appears that no you can not backup a database in an offline state based on this comment from BOL. Offline and Emergency appear to be the exceptions.
Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE. For information about database states, see Database States.However that being said the chances of the database state being the reason for your backup problem are slim. As said in the comments by @StrayCatDBA MSDB is required to do backups (among other things). You can look at the error for a backup by right clicking on your maintenance plan (assuming you are using one) and selecting view history.
Once you are in the Log File Viewer you can see what errors you have. Click on the + to drill down to details and then select each of the lines with the red X in turn to see what the current error is.
In this particular case we had a maintenance plan that was trying to back up databases that no longer existed. Once they were removed from the plan we stopped getting the errors.