Post reply

Maintainance plane failed because database offline

  • September 15, 2005 at 9:54 am

    #110005

    Is there a way to setup database backup plan that it won't fail when one of the database is offline?

    Thanks,

    tn

  • September 15, 2005 at 11:21 am

    #590259

    1. Set up separate maintenance plans for each database.

    2. Set each step to "go to next step" upon failure.

    -SQLBill

  • September 15, 2005 at 11:43 am

    #590275

    Thank you for your reply. 

    The server is a shared server, so there are quite a few databases.  Setting up separate maintenance plan for each database is not feasible.

    I am not familiar with set up steps in shechuled job.  Would you mind to help me out with the syntax? 

    Thanks,

    tn

  • September 16, 2005 at 4:18 am

    #590482

    How many db's have you got - it would have to more than about 20 to dismiss Bill's excellent suggestion.

    Cheers,

    Sim Lever

  • September 16, 2005 at 4:19 am

    #590483

    Ahhhhh glasshopper

    Cheers,

    Sim Lever

  • September 16, 2005 at 7:10 am

    #590551

    I don't use maintenance plans, I script my backup jobs and run them as jobs. You can create one job, where each step backs up a different database. One of the options is what action the job takes when the step fails.

    -SQLBill

  • September 16, 2005 at 8:05 am

    #590602

    Well, we have 56 databases on that server.  I would have follow Bill's advice but I don't own the server.  The person who owns the server expects jobs to be setup to run for all situations.  These databases will be moved to other servers within the next few weeks.  The owner of the server doesn't want to detach/delete those databases that were moved. Script seems to be the only option, except I don't know much about it!

    Thanks for all the input.  You guys are great.

    tn

  • September 16, 2005 at 12:35 pm

    #590713

    You might want to implement the following to perform a standard backup:

    Create a table in master DB

    with Database names, properties (such as  status column: suspect, offline etc)

    Loop thru all databases on your server by running:

    SELECT DATABASEPROPERTYEX ('YourDB','STATUS') AS Status

    Store the result in the table

    And then run something like that:

    declare @dbname varchar(40), @sql varchar(4000)

    select @dbname=min(name) from tbl_Databases where name not in ('tempdb','Northwind','pubs',) and status <>'Suspect'

    while @dbname is not null

    begin

     set @sql='backup database '+@dbname+' to disk=''D:\backup\'+@dbname+'.bak'' with init'

    -- print @sql

     exec (@sql)

     select @dbname=min(name) from tbl_Databases where name not in ('tempdb','Northwind','pubs') and status <>'Suspect' and name>@dbname

    end

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply