question

KK_SQL avatar image
KK_SQL 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.
sql-server-2005backup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

s we have two dbs in offline mode. so how can i exclude them
0 Likes 0 ·
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.
0 Likes 0 ·
JohnStaffordDBA avatar image
JohnStaffordDBA answered
There is an option in the DB Maintenance Plan to exclude any databases that are Offline.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think that's only a 2008 option. 2005 users are stuck.
0 Likes 0 ·
KenJ avatar image
KenJ answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.