…deploying quality ICT infrastructure and services
08030945000, 08086783266
Automating MS SQL Server Backups with SQLServerBooster
Preamble:
Automating database backups is a common challenge that developers face. When you manage multiple databases or your are supporting several clients who are using your database dependent software products, your concerns are grave. From informing and warning clients about the dangers of not backing up, some clients would still not backup regularly. If their system crashes and they loose their data, they become frustrated and begin to think that using electronic databases is not safe. The onus is on you the developer to ensure they can safely use their products to continue to be in business.
Automated backup software do exist, but the good ones with easy to use GUI’s are mostly expensive and way beyond the budget of most clients. We decided we should build something to take care of our client needs. So in the mist of several clients’ core demands which we are just trying to catch up with, we also had on our to-do list “setup a script to handle automated backups”, but pressing client requests has made us to continue pushing down this very important task. Then the pressure from backup preventable issues began to increase and we couldn’t postpone it anymore. Before we invest the time, we decided to search once more, and SQLServerBooster pops up. We downloaded and tried it. It’s exactly what we need. Designed by someone who had also faced the same problem a couple of years back.
Introducing SQLServerBooster
SQLServerBooster is a free application for DBA’s, developers or people that do management around Microsoft SQL Server ® (2014, 2012, 2008, 2005 and 2000 – all editions including Express) and need to make backups automatically, compress and send through FTP, Amazon S3 ®, Amazon Glacier ®, Windows Azure ®, Dropbox ® or network.
Supports Database Engine databases and SSAS (SQL Server Analysis Services) databases.
Features
- 100% Free of charge
- Supports Database Engine and SSAS (SQL Server Analysis Services) databases
- Compress (zip), 3 levels of compression, splits into multiple files, up to 95% compression
- 256-bit AES Encryption and Standard Encryption
- Sends to FTP, Amazon S3 ®, Amazon Glacier ®, Windows Azure ® and Dropbox ® or network folders, Proxy compatible
- Schedules backup tasks
- Configure it to removes old backups automatically
- Multiple instances and multiple-databases
- Creates .bak files -full, differential and transaction log- or script files
- Allow to backup multiple databases at once
- NO limitation in files size when you upload to any Cloud Service
- Send email notifications
- Backups log report
- Run as a service
- Elegant and efficient
How to use SQLServerBooster:
Download and install SQLServerBooster. The installation is quite easy, just follow the wizard. The people who would need something like these would definitely not need to be guided in handling installations.
Adding a backup task
SQLServerBooster gives you a Wizard that let you configure and schedule a backup task.
To create a backup task you have three different options to accomplish this:
Step 1 Welcome screen
Step 2 Serer type, authentication, server, database and user configuration
Step 3 Backup information, type and compress options
Step 4 Choose the backup type and make another copies
Step 5 Configure FTP, Amazon S3, Amazon Glacier, Windows Azure and Dropbox
Step 6 Configure email, deleted old backups and scheduler
Step 7 Summary
Step1: Welcome screen
Welcome screen
- Brief introduction about all the actions that you can configure on a backup task
- If you don’t want to see this step the next time, just check it
Step 2: Server, database and user configuration
Single backup
- Configure the server type, authentication method, server, database, user and password (*)(^)
- Helps to test if the information is correct
Multi-database backup
- Configure the server type, authentication method, server, database, user and password (*)(^)
- Select the databases that want to make a backup
- Helps to test if the information is correct
* Note: If the machine where SQLServerBooster it’s installed and MS SQL Server it’s installed are different, the authentication method has to be “SQL Server Authentication“, otherwise the SQLServerBooster Windows Service (SQLSBService.exe) will generate an error saying that because permissions was impossible to create the backup.
(^) Note: Backup “SSAS Remote Partitions” are not supported in this version.
Step 3: Backup information, type and compress options
Database Engine
1. Backup information
- Title: Will help to identify the task
- Description: Will be the Tooltip (help) to the task
2. Backup type:
- It is possible to choose between four different backup types:
- Full: A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data (*)
- Differential: A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data extents that have changed since the differential base. A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential (*)
- Transaction log: A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model) (*)
- Remote (Script): A backup that makes a dump of the database that includes the schema and the INSERT statements to load the data into the database
3. Compression: (ǂ)
- Use MS SQL Server Backup Compression: Since MS SQL Server ® 2008, Microsoft introduces the capability of compress natively the backup. Not all the editions support this option. I suggest always check this feature, if it is not available doesn’t matter, because SQLServerBooster internally always check it before apply. If you want to know more about this feature read this article in MSDN
- Compression level: It is possible select between three compression levels. While higher the level more CPU is demanded
- Encrypt backup: It is possible select between Standard, AES128, AES192 and AES256
- Split backup file: It is possible to split the compressed files into multiple files
- Delete backup after compress: Deletes the backup after being compressed
Analysis Services
1. Backup information
- Title: Will help to identify the task
- Description: Will be the Tooltip (help) to the task
2. Compression: (^)(ǂ)
- Use MS SQL Server Compression: It is possible to compress the SSAS databases natively by MS SQL Server. If you want to know more about this feature read this article in MSDN
- Compression level: It is possible select between three compression levels. While higher the level more CPU is demanded
- Encrypt backup: It is possible select between Standard, AES128, AES192 and AES256
- Split backup file: It is possible to split the compressed files into multiple files
- Delete backup after compress: Deletes the backup after being compressed
(^) Note: Backup “SSAS Remote Partitions” are not supported in this version.
(ǂ) Note: If the backup is not compressed then it won’t be able to upload to the Cloud (Amazon S3, Amazon Glacier, Windows Azure or Dropbox) or send through FTP.
Step 4: Choose the backup path and make another copies
Full, Differential and Transaction Log backup type
1. Backup name and path
- Single backup
- Backup physical name: If it’s a single backup, this field indicates the physical file name. Also at the end of the file SQLServerBooster concatenates the date and time with this format “yyyyMMdd24HHmmss“
- Multiple-database backup
- Multi-database prefix: If it’s a multi-database backup, a concatenation of the prefix and database name will be given to the physical file name. Also at the end of the file SQLServerBooster concatenates the date and time with this format “yyyyMMdd24HHmmss“. At the end the physical file name will look like this “PREFIX_DATABASENAME_yyyyMMdd24HHmmss”
- Database server path: Indicates where the backup file will be generated. It’s important that the directory has read/write permissions for all users. If it is a SSAS database backup it’s necessary to give permissions to read/write to the specific account that is running MS SQL Server Analysis Services; this account usually is “NT Service\MSSQLServerOLAPService“, read this to know how to make this change
- Is a remote database server?: In case the machine where SQLServerBooster it’s installed and MS SQL Server it’s installed is not the same. It has to specify a UNC path to be able to compress (in case this option was selected) and has access to copy to the other paths
2. Make a copy of the backup to these other paths
- Copy path: It’s possible to copy the backup to three different paths
- Delete backup after copy: This will delete the backup after being copied and uploaded to the Cloud or send through FTP
3. Helps to test that the paths exists
Remote (Script) backup type
1. Backup name and path
- Single backup
- Backup physical name: If it’s a single backup, this field indicates the physical file name. Also at the end of the file SQLServerBooster concatenates the date and time with this format “yyyyMMdd24HHmmss“
- Multiple-database backup
- Multi-database prefix: If it’s a multi-database backup, a concatenation of the prefix and database name will be given to the physical file name. Also at the end of the file SQLServerBooster concatenates the date and time with this format “yyyyMMdd24HHmmss“. At the end the physical file name will look like this “PREFIX_DATABASENAME_yyyyMMdd24HHmmss”
- Script path: This is a local path
- Batch size: The statement “GO” is used. This statement signals the end of a batch of Transact-SQL statements to the MS SQL Server utilities (*)
- Split into multiple files: If the database has a lot of data it is recommended to use this option. MS SQL Server Management Studio has limitations related with the files size to execute. Each file has a correlative to know the order in which has to be executed
- Size of the splited files (50 MB): The size of each file
2. Make a copy of the backup to these other paths
- Copy path: It’s possible to copy the backup to three different paths
- Delete backup after copy: This will delete the backup after being copied and uploaded to the Cloud or send through FTP
3. Helps to test that the paths exists
Step 5: Configure FTP, Amazon S3, Amazon Glacier, Windows Azure and Dropbox
Each one of these configurations have their own specifications, choose the one(s) that you will use:
- Configuring FTP
- Configuring Amazon S3
- Configuring Amazon Glacier
- Configuring Windows Azure
- Configuring Dropbox
Step 6: Configure email, delete old backups and scheduler
- Email configuration
- Send email when: It is possible send the email when exists “errors” or “errors and warnings” or always.
- Type of information: “Summary” just indicates if the backup was created successfully or had warnings/errors. “Detailed” includes the full log.
- Attach the log information: The log file can be attached in the following formats PDF, HTML, XLS, XLSX, CSV, TEXT (TXT), RTF and IMAGE (JPEG)
- To email addresses: The email’s that will receive the log email. If are more than one, just separate them by a comma “,”
- Email service: For a better usability you can select GMAIL, OUTLOOK (LIVE/HOTMAIL) and automatically some fields will be filled with the default information. Also it’s possible to set your custom information
- From email address: The email address that sends the log file
- Outgoing SMTP server: The server that will send the emails
- Port: The port used by the SMTP server
- Use authentication: Indicates if the SMTP server requires authentication
- Use SSL to connect: Indicates if SSL it’s needed
- User name / email: The user to connect to the SMTP server
- Password: User password
- Remove old backups configuration
- Remove backups older than: Indicates the files that will be deleted after specific number of days
- Type of backups to remove: The backups that meets the criteria of where they are stored
- Schedule configuration. Indicates which days and time of the day the backup task will be executed
- Options
- Test the email configuration. Helps to validate the email parameters
Step 7: Summary
Gives a summary of the configuration, just to confirm that all is set as was conceived.
***************************************
Our Endorsements
Prisec School Manager
Prisec School Manager is a comprehensive school management application that you can use to automate your school's administration.
RSS Feeds
Please encourage us by commenting