Only backing up the latest database

jonesricjonesric Posts: 25 Bronze 1
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.
Tagged:

Best Answer

  • Jon_KirkwoodJon_Kirkwood Posts: 326 Silver 4

    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.

    Jon Kirkwood | Technical Support Engineer | Redgate Software

Answers

Sign In or Register to comment.