Only backing up the latest database

jonesric

Hi all,

*SQL Backup 10.1.15*

This might be a bit of an odd question but bear with me - 

we have a Forcepoint Websense instance that logs activity and then creates a new database with a _n suffix (where n is an incremental numeric value) when the current one reaches a certain size (this is expected behaviour).  This means we have ended up with a number of databases like:

wslogdb_70_304
wslogdb_70_305
...
...
...
wslogdb_70_367
etc

As all the DBs apart from the current one are never written to, we don't need to back them up once the next DB has been created - the last full/full & diff is sufficient.

Is there a way, either scripted or via the GUI to get SQL Backup to only back up the latest DB without going into the GUI and updating the DB list every so often - there can be multiple incremental DBs created every day?

Thanks.

Jon_Kirkwood

Hi @jonesric

 

Thank you for reaching out on the Redgate forums regarding your SQL Backup question.

 It's an interesting scenario indeed spinning up new databases like this for logging activity.

From my understanding of your requirement the hardest part is locking down the latest db name automatically. Opening the GUI would bring up a list but you couldn't set a schedule as it is set against the one database.

I was looking through some potential solutions and think it may be feasible to do using a Stored Procedure or batch file.

 

When you set a schedule (SQL Backup > Actions > Schedule Backup Jobs) there is a step at the end where you can get a copy of the stored procedure or command line.

You could potentially take this code and expand it with some logic to find the last database in your sequence and then backup only that database.

 

 

I did a quick proof of concept with some databases abc_0, abc_1, abc_2

Using a select statement against the sys.databases table to find db's that matched the abc% structure, sorted descending and then returned the top value - this was abc_2 which I stored as a variable @last_db

 

Then set another variable @location as the filename of the backup including database name, then ran a backup. 

<p>DECLARE @last_db VARCHAR(30)</p><p>SET @last_db = (</p><p>SELECT TOP(1) name</p><p>FROM sys.databases</p><p>WHERE NAME like 'abc%'</p><p>ORDER BY name DESC</p><p>)</p><p><br></p><p>DECLARE @location VARCHAR(100)</p><p>SET @location = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\' + @last_db + '.sqb'</p><p>&nbsp;</p><p>BACKUP DATABASE @last_db</p><p>TO DISK = @location</p><p>WITH CHECKSUM, DIFFERENTIAL</p>

Looking in SQL Backup I can see that my differential backup ran on abc_2

 

 

 Then created db abc_3 and re-ran the script. Backup created and appeared in SQL Backup

 

 

From this I think it may be possible to create a stored procedure or batch file logic to find your database and schedule it to run you may be able to achieve the outcome you desire. Most of this would occur outside of SQL Backup but results can be viewed in application.

Noting that the differential backup likely won't run on a newly created database if there isn't a full backup done. Not knowing your full deployment this may not be an issue, but something to keep in mind.

Hope this is useful for your investigations.

jonesric

Hi Jon, thanks for your suggestion, I'll check it out 

jonesric

Hi @Jon_Kirkwood thank you very much for your suggestions.  My colleagues had a play around with them and are pretty close to what we were hoping for!