MSSQL Express Backup Script

There is a saying : “Better safe then sorry” well I’ve learned this the hard way a couple of times ;)…

Nowadays when I’m at a customer’s site and I have to change or do something regarding their MS SQL databases I always ask for a backup first. But sometimes the MS SQL server is a MS SQL Express or the customer doesn’t have the proper backup mechanism in place to backup the databases. Then this script comes in handy!

First you run the following query in the MS SQL Server Management Studio to create a stored procedure in MS SQL .

mssql-backup-script

MSSQL Express Backup Script

After this you can open a command prompt (or create a batch file) and use one the following lines depending on the MS SQL authentication to run the MS SQL backup :

mssql-backup-cmd
SQL Authentication

Windows Authentication

Remember to change the bold variables to your own needs and voila you’re “safe”!

Possible “Backuptypes” :
F = Full
D = Differential
L = Log

It is also possible to create a scheduled task which runs a batch file containing the following lines which automatically cleans up old database backup files after the backup :

Note : The backup retention in the script above is set to 5 days please change to your own requirement!

Marco van Baggum

Marco van Baggum

Works as a SDDC Architect for ITQ. More details can be found on the About page

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.