If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > sql server job for backup - generates error

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #16 (permalink)  
Old 07-29-10, 02:38
deiush deiush is offline
Registered User
 
Join Date: Jul 2010
Posts: 11
and...
not all db's must be backed up, and from a day to another, there may appear new db's (the list of db's is dynamic) - and the admin does not wish to modify the jobs
and... why do you redirect me to cmdexec? why do you think this could be better than xp_sqlmaint?
thank you
Reply With Quote
  #17 (permalink)  
Old 07-29-10, 09:32
rdjabarov rdjabarov is offline
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,611
Apparently you didn't read my post closely.
Quote:
why do you redirect me to cmdexec? why do you think this could be better than xp_sqlmaint?
Because XP_SQLMAINT is deprecated, and will be removed very soon. You need to use SQLMAINT.EXE, which is what the deprecated feature is using, including all the switches.

Dynamically backing up (and everything else, including reindexing, consistency checks, etc.) databases is what SQLMAINT does, when you select "All User Databases", so that your admin doesn't need to check is new databases got created every day.

And you still didn't make it more clear. What do you want the job or a step within a job to do when an error occurs?
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
Reply With Quote
  #18 (permalink)  
Old 07-30-10, 03:22
deiush deiush is offline
Registered User
 
Join Date: Jul 2010
Posts: 11
you are right for the first part... that wasn't the good question.
but... i've read again the msdn for sqlmaint... i understand from there that not only the xp_sqlmaint is deprecated, but sqlmaint itself (or, again, we don;t understand each other ).
then... not all user db's have to be backed up (don't ask me why... i just received the requirements)
and... in case of error - the situation in simple in this moment: the current job step that creates the backups - it takes all dbs (according to specifications) and sequentially creates backups. but, when an error occurs, the job terminates, goes out and... in the morning, the admin finds out that not all dbs were backed up. if the job would re-start, it would take again all dbs... and this is not good. that's why we want the job step, in case of error, to consider only the db's for which there is no backup (in that night). this would be easy by consulting the system tables or views, but... don't forget we have to take care of other jobs that are executing in parallel
so... i believe in a short time we will have to remove the usage of sqlmaint from out jobs, and from here - the next problem is the deletion of old backups. the only thing i know about deleting bkps is the xp delete file SP, which is not documented and... i think... a little bit unstable
thank you again... you are the only one who is discussing with me this problem
Reply With Quote
  #19 (permalink)  
Old 07-30-10, 11:42
rdjabarov rdjabarov is offline
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,611
You're right, SQLMAINT will be deprecated as well. That got me thinking and searching. According to M$ there is currently no existing option to programmatically access SelectedDatabases collection, so it looks like we'll have to resort to base T-SQL commands when building custom maintenance plans. Speaking of the latter, this is the only way how you will be able to meet your "new" requirements. I would recommend to introduce DatabasesToExclude table, and populate it with databases that you don't want to be backed up. Then do a LEFT OUTER JOIN on it from sys.databases where DatabasesToExclude.name is null. This way any newly created database will be included, while excluded databases will continue being skipped. To ensure that maintenance tasks do not overrun your maintenance window, you can also use creation of maintenance jobs per database "on the fly", with delete_level = 1 (on success). This way you can build the jobs, and then start them 1 by 1 without waiting for completion. Be careful on maximum number of jobs starting. There is a default of 20 jobs for TSQL subsystem, which will delay the rest of the started jobs until the number of running jobs goes below 20.
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
Reply With Quote
  #20 (permalink)  
Old 08-02-10, 09:03
deiush deiush is offline
Registered User
 
Join Date: Jul 2010
Posts: 11
1. good idea about how to exclude dbs... fortunately, we can build"" the lists of db for which to do / not to do backup using their names (like condition)
2. i like the idea of dynamically creating jobs for each db. still, i don;t understand how do you suggest to create them. their creation must also be automatically made.
Reply With Quote
Reply

Tags
backup, job, sqlmaint

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On