SQL Server is one of the most stable and widely used database engines around in corporate environments. Given that SQL Server 2008 R2 Express+ is available free of charge, is rock solid and can support databases up to 10GB in size, it's a logical choice for many web and desktop applications hosted in-house in corporate environments.
What can often be overlooked until it's too late (i.e. malware has corrupted the database, ransomware has encrypted it or the hard disk has given up the ghost) is a robust backup strategy. Commercial SQL Server backup tools can cost thousands but our method of choice is a robust Windows Powershell script. This script:
DBCC CHECKDBcommand to ensure the database is OK
The Powershell code looks as follows:
You'll notice that a few configuration files adjust the way the script functions. Specifically,
sql_server_backup_path.txt will simply contain the path (local to the SQL Server instance) to backup the databases to - in our example, we've set it to
sql_server_backup_servers.txt contains the list of machines that we should connect to, in this example, I'm connecting to 2 local machines and one remote machine:
sql_server_backup_exclude.txt contains a list of machines, instances or databases that should be excluded from the backup plan, i.e. in our example, we don't want to backup any databases from the IMF instance on REKII and we don't want to backup the
country_data database in the BIS instance on REKIII:
There are a couple of dependencies for all aspects of this script to work, i.e. you need the Powershell SQL snap-ins, the 7-zip command line utility and you should enter in your SMTP server and account credentials so you get the summary log email. I hope it offers at the very least a solid foundation for implementing a commercial SQL Server backup strategy.
As with all articles on Celtic Productions, this article is protected by international copyright laws. It may be linked to (we are of course most grateful of links to our articles), however, it may never be reproduced without the prior express permission of its owners, Celtic Productions. All code is provided as-is, without warranty of any kind, either express or implied.