T-sql – TSQL script to backup database using a driver table and stored procedure


I am in need to fulfill a client request that wants me to create a backup solution that includes a stored procedure that will pick up the backup information from a driver table that will hold all the parameters needed such as backup file path, type of backup, no. of days to retain the backup etc and based on that information the stored procedure will initiate the backup. I spoke to the client regarding the Ola's script for backing up the database but as per them they want an in-house developed script and is adamant about that. Can someone please help. I don't have much experience writing T-SQL code. I have a fair idea about what would be the end result but I don't know about the implementation logic. Please do let me know if someone already has a script that does the same.

Best Answer

There's a script out there that does almost the same thing and it's Ola's script. While the table is not programmable, the execution procedure is, so you can customize the job steps vs the table, it becomes more portable and easier to deploy in any environment. You'll find it hard to find another backup solution online that works as well to copy verbatim because solutions like Ola's already exist.

If they want an in-house built process utilizing T-SQL and you do not have the ability to write the T-SQL for the process, I would look at where you are now and how do you get to your end goal.

First, get your fundamentals together. Make sure you understand backups inside and out. Here are two good links to get started:

Backup ref 1

Backup ref 2

Next up on the list, T-SQL. You're in for a real bad time if you have to write all your code in T-SQL and if you don't know T-SQL. Check out Microsoft Developer Essentials. It comes with SQL Server Dev 2016 which is similar to Enterprise. It also comes with a 3 month trial of Pluralsite which you can and probably should immediately begin using to bolster your T-SQL. You can also check out these three books to help learn.

SQL in minutes

SQL Fundamentals

T-SQL Dev guide

MS Dev

In the end, you're going to have to pick a method for your process. This article talks about methods of administration that you will have to use to complete this task. You may have to incorporate all of them to achieve your goal.

I'd start there and progress, unfortunately I don't see any shortcuts in this scenario if you want something done correctly. Backups and data accessibility are huge in the land of database. See Brent Ozar's hierarchy of DBA needs for more reference there. I cannot stress the importance of doing backups correctly, it's one of the foundations for DBA's and most IT roles.

Once you get into this for awhile, I would recommend to come back here with specific questions about the process you have already began to write and supply us with good sample code. See here for best practice asking questions.