asked
backup failure
Hi we executing the Job through Maintenanceplan which performs backup of the databases. but its failing regularly. the error is > Executing the query "BACKUP DATABASE > [MIMS] TO DISK = > N'F:\\SQLData\\MSSQL.1\\MSSQL\\Backup\\MIMS\\MIMS\_backup\_201108110142.bak' > WITH RETAINDAYS = 14, NOFORMAT, > NOINIT, NAME = > N'MIMS\_backup\_20110811014222', SKIP, > REWIND, NOUNLOAD, STATS = 10 " failed > with the following error: "Database > 'MIMS' cannot be opened because it is > offline. BACKUP DATABASE is > terminating abnormally.". Possible > failure reasons: Problems with the > query, "ResultSet" property not set > correctly, parameters not set > correctly, or connection not > established correctly. pls can any body suggest on this.
As far as I can tell from the information provided, you have a maintenance plan with a Backup task, which backs all databases. You have a database - MIMS - which is offline during the backup. Is the database MIMS Offline, in Single User Mode etc, or is the error message misleading?
This happens to folks a lot when they have a backup routine that backs up all database but does not exclude those that are in a state where they can't be backed up, such as being taken offline. It has been awhile since I have played with maintenance plans as I do all my maintenance tasks with custom scripts. In my scripts I exclude databases that are loading, recovering, offline, etc. Check in the maintenance plan to see if you can exclude databases based on status. I know you can select which databases to backup but there are negatives to doing that such as a new database being created and you forgetting to manually include it. If the MIMS database truly needs to be offline for reasons such as it will be deleted at some point, try detaching it rather than leaving it offline.
With SQL Server 2005, you can't. You have basically three options: 1) Switch to scripts (like Ola Hallengrens as mentioned by @KenJ) 2) Specify which databases to backup, instead of "ALl databases". Not very "automagic", but hey - how often do you add new databases and how hard is it to include "Tick the new database in Backup plan" a part of your routines when creating new databases? 3 (and my preferred choice, regardless of if you choose (1) and/or (2)): Don't have databases offline, detach them instead.
If you want to skip offline databases with a SQL 2005 maintenance plan, you must change the maintenance plan from backup all to backup specific and exclude the two databases in question. Of course this means it won't automatically pick up new databases as you add them. If you can upgrade to SQL 2008, the new maintenance plans have an option to skip offline databases. If you can't upgrade to SQL 2008, but don't like the brittleness of the 2005 maintenance plans, you can use custom backup scripts. If you don't want to write your own from scratch, you can't go far wrong with Ola Hallengren's solutions - [
http://ola.hallengren.com/][1]. He provides highly regarded maintenance scripts, including one for backups. [1]:
http://ola.hallengren.com/
