SQL Server Backups
This article will list out the various types of backups you can create for databases hosted on an SQL Server, explore methods for creating backups manually and automatically, as well as list software/services that may assist you with backing up your data.
If you are seeking information on setting up an SQL Server to store your data in Microvellum, consult this series of tutorial articles on Microvellum’s Knowledge Network:
Different types of SQL Server backups
Full: Backs up everything in the database, including objects, system tables, data, and transactions that occur during the backup. With a full backup, one can restore a database to the exact state it was in when it was backed up. Full backups won’t truncate your transaction log but if your database is in full recovery, you should also consider transaction log backups.
Differential: Retains data that has changed and transactions that have occurred since the last full backup. Differential backups are only useful if used in tandem with a full backup but allow you to delete/remove previous differential backups.
Transaction log: Backs up all the transactions that have occurred since the last log backup or truncation, then truncates the transaction log. This will capture all transaction information, both DML and DDL, that has occurred on the database. With a transaction log backup, you can restore a database to a particular point in time, such as right before a data loss event.
File-group and File: This type of backup is best for larger databases. This type of backup will store all related data in files or file groups (one or more). To use file backups to successfully restore a database, the transaction log also must be backed up to cover all the file groups from beginning to end.
Copy-only: This backup type is usually used on an ad-hoc basis and so as not to disturb any existing process of database backups, since the transaction log copy-backup will ignore the copy backup. Otherwise, this is the same as a full database backup. Copy backups can’t be used for differential backups and transaction log backups.
Manually Backing Up SQL Data
NOTE: The first method of manually backing up your SQL databases relies on SQL Server Management Studio. If you do not have SQL Server Management Studio installed, Microvellum recommends downloading the latest generally available (GA) version from this page.
The most straightforward method of backing up the data in your SQL database is to simply manually do so in SQL Server Management Studio (SSMS). There is a native task in SSMS allowing users to manually back up their data via a simple interface. For information on how to perform this process, consult this article.
If using SQLCE, an equally simple method to manually back up your data is to locate your data folder, then copy it directly into your backup location via file explorer. Locating your data folder can be done during Toolbox usage by clicking on “Help” then “Browse to Microvellum Data” from the Toolbox interface. Consult this article for an example of how to back up data locally with this method.
Automating SQL Server Backups
While manually backing up SQL Server databases is an effective method for ensuring data consistency, depending on the size of one’s database, the process might be more laborious and less efficient than one’s workflow might permit. As such, it may be preferable to automate the data backup process. There are different methods for doing this based on one’s software setup:
Maintenance Plans (Standard)
If using a standard SQL Server, one can utilize the SQL Server Agent, a tool that enables a user to set up tasks that can be scheduled to consistently perform, including the backing up of one’s SQL server data. This function is referred to as a maintenance plan. The SSMS has a wizard to walk users through the process from start to finish.
- To set up a maintenance plan, open SSMS, connect to the SQL Server instance, expand the “Management” folder, then right-click the “Maintenance Plans” folder and create a new maintenance plan.
- From the maintenance plan configuration pan, drag the “Back Up Database Task” into the center UI, then open the task to edit it.
- From there, select from the available options what type of SQL backup you want to create (from the options listed above), as well as which databases specifically you would like to be copied, then click “OK”.
- Select the location you intend to store your database backups, then whether you want to create a sub-directory for each database (it is recommended that you enable this option if copying multiple databases).
- Click on the calendar icon, select “Subplan Schedule”. It is recommended to keep the job’s schedule type to “Recurring”. Set the parameters of how frequently you want the backup task to run, the time the task occurs, any start or end point you wish to add to the task and write a description of the task.
- Click “OK” and save the changes you’ve made. Your server will now automatically back up in accordance with your specifications. You can perform all these steps again if you want to set up different types of backups with any other databases on your server in the future.
Task Scheduler (Express)
SQL Express servers do not have access to the SQL Server Agent functions in SSMS, and as such, an alternative method is required to automate the backup process. You will need to create a batch file of Trans-SQL script, then utilize the Windows Task Scheduler to automate it.
NOTE: In SQL Express, to enter a Transact-SQL statement, system procedure, or script file, you will need access to the SQLCMD utility. This is built into the program of all SQL 2014 and lower versions, but if using SQL 2016 or newer, it will be necessary to download the utility. You can download it here.
1. Connect to your SQL Express instance and create a new stored procedure in your master database. To do this, navigate to Databases > System Databases > master > Programmability > Stored Procedures > New Stored Procedure (or just “Stored Procedure”, if this option is not available.)2. Within the console, paste the following sample script provided by Microsoft. Plug in any necessary information (database name, backup location, etc.) and save it as a stored procedure.3. After creating the procedure file in SSMS, open a text editor and create a batch file called Sqlbackup.bat, then copy one of the following commands into the document based on the type of backup you intend to create. (Note, the following examples use D:\SQLBackups\ as an example location.)
- Full: // Sqlbackup.bat sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"
- Differential: // Sqlbackup.bat sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType='D'"
- Transaction Log: // Sqlbackup.bat sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"
4. With your batch file created, and your backup procedure saved, open the Windows Task Scheduler.
5. Create a Basic Task, naming it appropriately (“SQLBackup” or something similar, for instance), then click Next.
6. Select the appropriate interval of time for the Trigger, depending on how frequently you would like your data to be backed up (it is suggested one select “Daily”), then click Next.
7. Set the start date (the current date is recommended) and the repeating time desired for the task to be undertaken. Then, click Next.
8. Select “Start a Program” in the Action type. Click “Browse” and insert your batch file. Click Next.9. Enable “Open the Properties dialog for this task when I click Finish” checkbox, then click Finish.10. In the General tab, configure the remaining setup options based on your SQL Express server settings: Ensure the account being used to authorize the backups has Read and Execute permissions to launch sqlcmd, if using Windows Authentication, ensure the task owner has permissions to do SQL backups.11. Test the process by executing it. If there is an error, review and retry the steps for the process.
Third Party Software
A wide array of software and online tools are available to users who wish to have their backup process automated without needing to use SSMS. It is recommended to search independently or contact your business’s IT professional to find a service/software that aligns with your company’s needs most closely. The options listed below are services/software available for client usage for the purpose of backing up databases.
Purchased Software
- Vembu BDR Suite – A cloud-based backup service offering a wide range of editions, including one specifically dedicated to backing up databases, including SQL Server, PostgreSQL, MySQL, MS Exchange, and SharePoint. The BDR service’s instance backups can be used for replication and migration, as well as recovery. The backups stored within the system are reformatted into tables, allowing one to restore specific tables rather than the entire database, if needed (though full database recovery is available). The BDR Suite offers straightforward pricing plans based on the specific type of data being backed up.
- N-able Cove Data Protection – A SaaS platform supporting automated backup, recovery, and replication options for MySQL, SQL Server, and Oracle databases, as well as local, remote, and cloud-based storage. Data is protected by AES-256 encryption, and storage is mirrored on a secondary data center to prevent data loss. The platform is available for purchase, requiring one to ask for a quote.
Free Software
- SQL Backup Master – An SQL backup tool designed to work in tandem with cloud storage devices such as OneDrive, Amazon S3, Azure, Google Storage, and Dropbox. The software also can back up databases locally and to FTP servers. The software is offered in both a free (Basic) and a paid (Pro) version.
- LiteSpeed for SQL Server – A backup tool specifically designed to work with SQL servers, capable of backing up databases to multiple destinations, including cloud storage, local drives, and shared network locations. Users can back up and restore directly from Azure, Google Storage, Amazon S3, IBM, and TSM. LiteSpeed supports SQL Server 2005, 2008, 2012, 2016, and 2019. The tool comes in both a free and a paid version, requiring signup to get a pricing plan.
For further information on the various types of backups in an SQL Server, or the process of setting up backups, visit one of the following articles from Microsoft:
Related Articles
Microvellum Data Backup Tips
Here are a couple steps you can take to backup your Toolbox data prior to updating your software: Information on System used in sample paths: Name of Library configuration: Advanced_Frameless_Library_English_v036_1 Database Type: SQLCE Files To Be ...Transferring Data to an SQL Server Database (Unified Work Order Database)(Tutorial 4)
This is the fourth in a series of tutorials presenting the steps necessary to install and configure a new SQL Server database to work within Microvellum. It focuses on how to transfer data to new SQL Server databases. Click here to view the third ...Installing SQL Server Express Edition (Tutorial 1)
This is the first in a series of tutorials presenting the steps necessary to install and configure a new database. Later tutorials will present the steps to configure the SQL Server database instance to work with Microvellum. We recommend the ...Configuring an SQL Express Database (Tutorial 2)
This is the second in a series of tutorials presenting the steps necessary to install and configure SQL Server to work with Microvellum. This tutorial focuses on the concluding steps when configuring the new database instance you created previously ...Connecting Microvellum to an SQL Database (Unified Work Order Database)(Tutorial 3)
This is the third in a series of tutorials presenting the steps necessary to install and configure a new SQL Server database to work within Microvellum. It focuses on how to configure Microvellum to connect to the new SQL Server databases you have ...
Recent Articles
SQL Server Backups
This article will list out the various types of backups you can create for databases hosted on an SQL Server, explore methods for creating backups manually and automatically, as well as list software/services that may assist you with backing up your ...Toolbox Release Notes | Build 25.1.0114.641
The following release notes apply to Toolbox build 25.1.0114.641. UI Fixes Several issues were reported and discovered within the OEM 2025 user interface. The following issues have been resolved: Fig. 1: Excess commands on the tool strip being ...Toolbox Release Notes | Build 25.1.102.641
The following release notes apply to Toolbox build 25.1.102.641 Toolbox OEM 2025 Work smarter, and faster with Toolbox 2025 (OEM). With an OEM Subscription, you’ll gain access to the latest toolsets and innovative features of Toolbox 2025. ...What’s New in Toolbox 2025 (OEM)
Work smarter, faster with Toolbox 2025 (OEM). With an OEM Subscription, you’ll gain access to the latest toolsets and innovative features of Toolbox 2025. Improvements Toolbox UI Overhaul OEM 2025 introduces several updates to the appearance and ...Toolbox Release Notes | Build 24.1.1206.641
The following release notes apply to Toolbox build 24.1.1206.641 MV Server Nesting Fix There was a reported issue occurring when users attempted to remotely process work orders using a nesting station on MV Server. When attempting to process the work ...