Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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)?

share|improve this question
What state was msdb in? Since backup history is recorded in msdb, i don't think you should take any backups if msdb wasn't online. Also, sql agent jobs are stored in msdb, so how were they running if msdb wasn't online? – StrayCatDBA May 2 at 11:28
msdb was in state other then online that doesn't mean it was offline. I tried to find some information for windows log but didn't find any information on change of state on msdb. Backup of msdb was skipped in some instances otherwise it's being backed up properly most of the time – sumanmodi May 2 at 12:13

migrated from stackoverflow.com May 2 at 15:11

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

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.

Maintenance plan 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.

Log File Viewer

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.

share|improve this answer
Thanks Kenneth for answer. We have a sql job which does the backup and if the status is not online then it skips so no error generated. What i now can assume on the basis of BOl comment is except Offline or Emergency state I can backup the database with any state, please correct me if I am wrong. – sumanmodi May 3 at 8:44
Yes, you are correct. Offline and Emergency are the two states you can not backup even with NO_TRUNCATE. I've corrected my answer above. @Raj is correct. You really should add some logging to your process. You might also add some error checking. Also don't forget that if your database is not ONLINE (suspect for example) then you will need to add the NO_TRUNCATE to the statement. Also if your MSDB database is going into a non ONLINE state you might try to figure out why. It's an important database and I would be worried about something going wrong with it. – Kenneth Fisher May 3 at 11:56
Kenneth appreciate your help. I was just wondering, how and where i can check why msdb status is changing. – sumanmodi May 3 at 15:11
Honestly I'm truly shocked that it is. The first thing to do is to add the logging and figure out what status it is going to. Also can you share your query you are using to check? It's possible it has a bug in it somewhere. Once you know the query is working correctly, and you have the status that MSDB is going to maybe we can figure out why it's happening. – Kenneth Fisher May 3 at 15:21
up vote 1 down vote

The possible values in the State and State_Desc columns of sys.databases are:

0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
7 = COPYING (Applies to Windows Azure SQL Database)

From your question, I am assuming that the backup job is called using a script, which queries and gets the value of state column, backs up if 0, or skips to next DB if state <> 0. Sadly with this approach, there would be no exception handling. Why don't you add code where the state is checked and log the DB name and value, so you can report on the same at the end of you job, like - "msdb state = X. Not backed up"?

From the available values, I am unable to think what could the value of msdb be, to prevent backup, but still allow the job to run.

Raj

share|improve this answer
Yes Raj, you are right their is no mechanism here of knowing what was the state of the database at that time. So I will go by your suggestion that add a table to store the history and any exception arising due to backup. And will change the code to backup any database except Offline or Emergency – sumanmodi May 3 at 9:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.