Post reply

Query to backup the databases on a SQL instance using a config table

  • April 8, 2017 at 11:41 pm

    #336360

    Hi Folks,

    I am looking for a script that would help me take the backup of different types e.g (Full, Diff and TLog) for all the databases using a driver or a master table and then run a stored procedure to get the details from the table and execute the desired backup type based on the configuration values. I do not want Ola's solution so please do not point me there.

    Thanks!

    Regards,
    Feivel

  • April 9, 2017 at 5:41 am

    #1937337

    Why don't you want to go with ola's scripts? Maybe you should read more about its usage.
    What about http://minionware.net/reindex/ ?
    If still not interested, then you have the scripts in this site - http://www.sqlservercentral.com/search/?q=reindex&t=s&sort=relevance

    Igor Micev,My blog: www.igormicev.com

  • April 9, 2017 at 6:39 am

    #1937340

    ffarouqi - Saturday, April 8, 2017 11:41 PM

    Hi Folks,

    I am looking for a script that would help me take the backup of different types e.g (Full, Diff and TLog) for all the databases using a driver or a master table and then run a stored procedure to get the details from the table and execute the desired backup type based on the configuration values. I do not want Ola's solution so please do not point me there.

    Thanks!

    Regards,
    Feivel

    What parts of the backup do you want to control with your table?  What have you done thus far?

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • April 9, 2017 at 10:27 am

    #1937348

    Igor Micev - Sunday, April 9, 2017 5:41 AM

    Why don't you want to go with ola's scripts? Maybe you should read more about its usage.
    What about http://minionware.net/reindex/ ?
    If still not interested, then you have the scripts in this site - http://www.sqlservercentral.com/search/?q=reindex&t=s&sort=relevance

    All of that is about reindexing.  The OP is asking about backups.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 9, 2017 at 11:02 am

    #1937349

    ffarouqi - Saturday, April 8, 2017 11:41 PM

    Hi Folks,

    I am looking for a script that would help me take the backup of different types e.g (Full, Diff and TLog) for all the databases using a driver or a master table and then run a stored procedure to get the details from the table and execute the desired backup type based on the configuration values. I do not want Ola's solution so please do not point me there.

    Thanks!

    Regards,
    Feivel

    To be honest, you already have such a system.  Maintenance Plans for backups are just fine and they're point and shoot.  No sense in making something more complex.  Just make a Maintenance Plan for each type of backup you want to do and enjoy the leisure time you've just created for yourself.  I also don't understand why you wouldn't want to use Ola's solution.  You could easily make a table with the necessary parameterized calls to his solution and, again, enjoy the leisure time you've just created for yourself.  If you were hell bent on infinitely detailed control, you could build your table of controls and then write a stored procedure that would read those controls and generate the calls to Ola's scripts to do exactly what you want. 

    Don't forget about the added complexities that most people forget about when they write their own code.  For example, what of READ ONLY file groups?  What of multiple file groups?  Should you be backing up by file group or by simple database call out?  Do you actually need to do all of that along with the possibility of redeveloping the proverbial wheel when it comes to when the jobs will run and how will you actually schedule such a thing?  How will you ensure that the new directories for backups of new databases already exist or are created if they don't?  How will your carefully configured table auto-magically deal with the appearance of a new database or the deletion of an exiting database? 

    While I seriously admire the gumption of anyone willing to take on such a task (been there and done that myself), take the work that someone has already developed and tested the hell out of and use it.  Although having backups and testing restores in of paramount importance for all DBAs, there's probably no reason other than personal preference to redevelop this wheel.

    And, let's stop and think about it all... you're not actually asking for how to do it.  You're asking for a 3rd party to provide you a script.... just like Ola's or Minionware.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 9, 2017 at 11:10 pm

    #1937373

    Jeff Moden - Sunday, April 9, 2017 11:02 AM

    ffarouqi - Saturday, April 8, 2017 11:41 PM

    Hi Folks,

    I am looking for a script that would help me take the backup of different types e.g (Full, Diff and TLog) for all the databases using a driver or a master table and then run a stored procedure to get the details from the table and execute the desired backup type based on the configuration values. I do not want Ola's solution so please do not point me there.

    Thanks!

    Regards,
    Feivel

    To be honest, you already have such a system.  Maintenance Plans for backups are just fine and they're point and shoot.  No sense in making something more complex.  Just make a Maintenance Plan for each type of backup you want to do and enjoy the leisure time you've just created for yourself.  I also don't understand why you wouldn't want to use Ola's solution.  You could easily make a table with the necessary parameterized calls to his solution and, again, enjoy the leisure time you've just created for yourself.  If you were hell bent on infinitely detailed control, you could build your table of controls and then write a stored procedure that would read those controls and generate the calls to Ola's scripts to do exactly what you want. 

    Don't forget about the added complexities that most people forget about when they write their own code.  For example, what of READ ONLY file groups?  What of multiple file groups?  Should you be backing up by file group or by simple database call out?  Do you actually need to do all of that along with the possibility of redeveloping the proverbial wheel when it comes to when the jobs will run and how will you actually schedule such a thing?  How will you ensure that the new directories for backups of new databases already exist or are created if they don't?  How will your carefully configured table auto-magically deal with the appearance of a new database or the deletion of an exiting database? 

    While I seriously admire the gumption of anyone willing to take on such a task (been there and done that myself), take the work that someone has already developed and tested the hell out of and use it.  Although having backups and testing restores in of paramount importance for all DBAs, there's probably no reason other than personal preference to redevelop this wheel.

    And, let's stop and think about it all... you're not actually asking for how to do it.  You're asking for a 3rd party to provide you a script.... just like Ola's or Minionware.

    Thanks! Jeff. I understand you're point and I may only need a general script so I can modify it to my needs. I am not so great in writing tsql codes hence I can only imagine how the solution would look like but have no clue about its implementation logic. Yes you are right that I am not asking someone to write a code for me or reinvent the wheel but I am only asking if some kind soul has done something like this in the past and can share the script with me and the community...same as what Ola did.

  • April 10, 2017 at 7:53 am

    #1937432

    ffarouqi - Sunday, April 9, 2017 11:10 PM

    Jeff Moden - Sunday, April 9, 2017 11:02 AM

    ffarouqi - Saturday, April 8, 2017 11:41 PM

    Hi Folks,

    I am looking for a script that would help me take the backup of different types e.g (Full, Diff and TLog) for all the databases using a driver or a master table and then run a stored procedure to get the details from the table and execute the desired backup type based on the configuration values. I do not want Ola's solution so please do not point me there.

    Thanks!

    Regards,
    Feivel

    To be honest, you already have such a system.  Maintenance Plans for backups are just fine and they're point and shoot.  No sense in making something more complex.  Just make a Maintenance Plan for each type of backup you want to do and enjoy the leisure time you've just created for yourself.  I also don't understand why you wouldn't want to use Ola's solution.  You could easily make a table with the necessary parameterized calls to his solution and, again, enjoy the leisure time you've just created for yourself.  If you were hell bent on infinitely detailed control, you could build your table of controls and then write a stored procedure that would read those controls and generate the calls to Ola's scripts to do exactly what you want. 

    Don't forget about the added complexities that most people forget about when they write their own code.  For example, what of READ ONLY file groups?  What of multiple file groups?  Should you be backing up by file group or by simple database call out?  Do you actually need to do all of that along with the possibility of redeveloping the proverbial wheel when it comes to when the jobs will run and how will you actually schedule such a thing?  How will you ensure that the new directories for backups of new databases already exist or are created if they don't?  How will your carefully configured table auto-magically deal with the appearance of a new database or the deletion of an exiting database? 

    While I seriously admire the gumption of anyone willing to take on such a task (been there and done that myself), take the work that someone has already developed and tested the hell out of and use it.  Although having backups and testing restores in of paramount importance for all DBAs, there's probably no reason other than personal preference to redevelop this wheel.

    And, let's stop and think about it all... you're not actually asking for how to do it.  You're asking for a 3rd party to provide you a script.... just like Ola's or Minionware.

    Thanks! Jeff. I understand you're point and I may only need a general script so I can modify it to my needs. I am not so great in writing tsql codes hence I can only imagine how the solution would look like but have no clue about its implementation logic. Yes you are right that I am not asking someone to write a code for me or reinvent the wheel but I am only asking if some kind soul has done something like this in the past and can share the script with me and the community...same as what Ola did.

    And that brings us full circle back to the original question.  Ola's stuff works fine, fails safe, and drains to the bilge.  It does all the things you might want, is well documented on his site, is maintained and upgraded, and there's a huge following because it works so well for so many people. 

    So why did you state that you didn't want to use it?  The implementation logic for Ola's stuff can be as simple as creating one job per schedule type or backup type and you're done.  Virtually no T-SQL to write and Ola's online documentation tells you what each parameter is for and gives examples of how to call his code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 10, 2017 at 8:03 am

    #1937437

    Jeff Moden - Monday, April 10, 2017 7:53 AM

    ffarouqi - Sunday, April 9, 2017 11:10 PM

    Jeff Moden - Sunday, April 9, 2017 11:02 AM

    ffarouqi - Saturday, April 8, 2017 11:41 PM

    Hi Folks,

    I am looking for a script that would help me take the backup of different types e.g (Full, Diff and TLog) for all the databases using a driver or a master table and then run a stored procedure to get the details from the table and execute the desired backup type based on the configuration values. I do not want Ola's solution so please do not point me there.

    Thanks!

    Regards,
    Feivel

    To be honest, you already have such a system.  Maintenance Plans for backups are just fine and they're point and shoot.  No sense in making something more complex.  Just make a Maintenance Plan for each type of backup you want to do and enjoy the leisure time you've just created for yourself.  I also don't understand why you wouldn't want to use Ola's solution.  You could easily make a table with the necessary parameterized calls to his solution and, again, enjoy the leisure time you've just created for yourself.  If you were hell bent on infinitely detailed control, you could build your table of controls and then write a stored procedure that would read those controls and generate the calls to Ola's scripts to do exactly what you want. 

    Don't forget about the added complexities that most people forget about when they write their own code.  For example, what of READ ONLY file groups?  What of multiple file groups?  Should you be backing up by file group or by simple database call out?  Do you actually need to do all of that along with the possibility of redeveloping the proverbial wheel when it comes to when the jobs will run and how will you actually schedule such a thing?  How will you ensure that the new directories for backups of new databases already exist or are created if they don't?  How will your carefully configured table auto-magically deal with the appearance of a new database or the deletion of an exiting database? 

    While I seriously admire the gumption of anyone willing to take on such a task (been there and done that myself), take the work that someone has already developed and tested the hell out of and use it.  Although having backups and testing restores in of paramount importance for all DBAs, there's probably no reason other than personal preference to redevelop this wheel.

    And, let's stop and think about it all... you're not actually asking for how to do it.  You're asking for a 3rd party to provide you a script.... just like Ola's or Minionware.

    Thanks! Jeff. I understand you're point and I may only need a general script so I can modify it to my needs. I am not so great in writing tsql codes hence I can only imagine how the solution would look like but have no clue about its implementation logic. Yes you are right that I am not asking someone to write a code for me or reinvent the wheel but I am only asking if some kind soul has done something like this in the past and can share the script with me and the community...same as what Ola did.

    And that brings us full circle back to the original question.  Ola's stuff works fine, fails safe, and drains to the bilge.  It does all the things you might want, is well documented on his site, is maintained and upgraded, and there's a huge following because it works so well for so many people. 

    So why did you state that you didn't want to use it?  The implementation logic for Ola's stuff can be as simple as creating one job per schedule type or backup type and you're done.  Virtually no T-SQL to write and Ola's online documentation tells you what each parameter is for and gives examples of how to call his code.

    Jeff: Again circling back to the same question. I clearly stated that I am not looking for Ola's solution. I know it is great, what it does, people use it and it works for them etc but I am looking for a different approach or methodology to address the same problem. I am not against Ola's script...it is fantastic but as explained I am looking for an alternate approach. If you have something that I need the I would really appreciate your help but if you don't then I won't be keeping up with the discussion anymore because it is not going to help me reach a solution.

    Thanks

  • April 10, 2017 at 8:32 am

    #1937449

    ffarouqi - Monday, April 10, 2017 8:03 AM

    Jeff: Again circling back to the same question. I clearly stated that I am not looking for Ola's solution. I know it is great, what it does, people use it and it works for them etc but I am looking for a different approach or methodology to address the same problem. I am not against Ola's script...it is fantastic but as explained I am looking for an alternate approach. If you have something that I need the I would really appreciate your help but if you don't then I won't be keeping up with the discussion anymore because it is not going to help me reach a solution.

    Thanks

    Yep.  That part was clear from the git.  I'm trying to understand why you're hell bent on using a driver table for such a thing which, IMHO, is an unnecessary complication especially for someone that has admitted to not being so hot at T-SQL that would modify such code to make it fit their needs. 😉

    Moving on from that question and sorry to disappoint but I've not written such code (never saw the need) that uses a driver table to control the backups and I don't know of anyone that has.

    About the closest I've come to that is to write some code that backups up all of the database for FULLs with a table that excludes certain "sandbox" databases by name or by name pattern and reads the system tables to exclude any databases from transaction log backups if they're in the SIMPLE Recovery Model.  It does have the ability to send an email for any given backup failure.  It would require some pretty good modification to take it's action based on a driver table especially if the driver table also had pertinent scheduling information as to when to run any given backup.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 10, 2017 at 8:43 am

    #1937451

    Jeff Moden - Monday, April 10, 2017 8:32 AM

    ffarouqi - Monday, April 10, 2017 8:03 AM

    Jeff: Again circling back to the same question. I clearly stated that I am not looking for Ola's solution. I know it is great, what it does, people use it and it works for them etc but I am looking for a different approach or methodology to address the same problem. I am not against Ola's script...it is fantastic but as explained I am looking for an alternate approach. If you have something that I need the I would really appreciate your help but if you don't then I won't be keeping up with the discussion anymore because it is not going to help me reach a solution.

    Thanks

    Yep.  That part was clear from the git.  I'm trying to understand why you're hell bent on using a driver table for such a thing which, IMHO, is an unnecessary complication especially for someone that has admitted to not being so hot at T-SQL that would modify such code to make it fit their needs. 😉

    Moving on from that question and sorry to disappoint but I've not written such code (never saw the need) that uses a driver table to control the backups and I don't know of anyone that has.

    Jeff - I did not want to say this earlier but I am hell bent in finding such a solution in order to fulfill my client's appetite. I tried hard convincing them about Ola's scripts and many of the similar scripts in the market but they are stuck on using this approach. One of the DBA's there is a nut ball. I haven't worked extensively on writing T-SQL code and my knowledge is fairly restrained on that part, but I can't dictate the client. I am a little frustrated at this time and I can't think in a clear direction. I have definitely seen a few cases of this implementation and most importantly a couple of colleagues use the same script as what I need but unfortunately they are not willing to share the same and hence I ended up asking in the forum.

  • April 10, 2017 at 12:50 pm

    #1937490

    ffarouqi - Monday, April 10, 2017 8:43 AM

    Jeff - I did not want to say this earlier but I am hell bent in finding such a solution in order to fulfill my client's appetite. I tried hard convincing them about Ola's scripts and many of the similar scripts in the market but they are stuck on using this approach. One of the DBA's there is a nut ball. I haven't worked extensively on writing T-SQL code and my knowledge is fairly restrained on that part, but I can't dictate the client. I am a little frustrated at this time and I can't think in a clear direction. I have definitely seen a few cases of this implementation and most importantly a couple of colleagues use the same script as what I need but unfortunately they are not willing to share the same and hence I ended up asking in the forum.

    I've seen that from clients before - mostly from shops that are not primarily SQL Server but have some SQL Server instances. And they will not use other scripts no matter how many times you explain things to them, how you explain it to them, they just won't budge. Don't understand it myself but it is what it is. And if they have to have some COTS that uses SQL Server and don't want any type of backup other than those done with homegrown SQL scripts...kind of puts you in a bad spot. It is their company and their rules. 
    Take a look at sys.databases since it has some of the information you are looking for. In terms of differentials, that depends on how you are determining when/if a differential should be done. You could check the backup tables in msdb if that's needed in determining whatever logic is driving some of this. Backup types in backupset and the physical backup file is in backupmediafamily. There are quite a few different types of scripts out there - just search on: backup database script sql
    You can also find some scripts up here if you search the scripts. Check this link:
    SQLServerCentral Search Backup Scripts
    But you would want to know what the drivers are for whatever kind of backups they want. Some or a lot of that is determined by values in the tables I mentioned. You'll likely need to tweak whatever scripts you find.
    If they are hell bent on the config table - seen that one before too, you would still want to reference those tables to give you an idea and check the scripts that are out there. And they could also ask you to update the config table with a script so you'd likely use those tables. Or more likely want to control the backups with the config table so make sure you have necessary constraints on the table. I doubt anyone will have exactly what you need so just search through some of the examples and know about those tables.

    Sue

Viewing 11 posts - 1 through 10 (of 10 total)

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