Top 3 Ways for MSSQL Backup in SQL Server 2012, 2017, 2019
Microsoft SQL Server (MSSQL) is widely used in enterprise deployments. If you are in need of backing up MSSQL databases, read this page to get the top 3 ways.
What is MSSQL and why is MSSQL backup important
Microsoft SQL Server (MSSQL) is a relational database management system developed by Microsoft. It can help to store and retrieve data as requested by other software applications which may run either on the same computer or on another computer across a network (including the Internet).
There are a dozen different editions of Microsoft SQL Server aimed at different audiences and different workloads, such as Enterprise edition, Standard edition, Web edition, and Evaluation edition (Trial edition), etc.
If your enterprise is using Microsoft SQL Server, it is important to backup MSSQL databases because:
Potential security threats: media failure, user errors, a damaged disk drive, permanent loss of a server, or even natural disasters can cause data damage or data loss.
Instant disaster recovery: you can easily restore damaged or lost data from the backup copy so as to keep your business ongoing.
Database administration: sometimes you may need to copy a database from one server to another or archive a database, etc.
Then, how to do MSSQL database backup? Please read on.
In MSSQL backup database can be done by many ways. I'll mainly introduce 3 effective solutions to run Microsoft SQL Server backup.
SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. You can use it to query, design, and manage your databases and data warehouses whether they are on your local computer or in the cloud.
As an example, I will illustrate how to backup database in SQL Server 2012 SSMS:
1. Download and install SQL Server Management Studio on your computer.
2. Start SQL Server Management Studio and the Connect to Server window opens. After you've completed all the fields, select Connect.
3. In Object Explorer, expand the server tree. Then, expand Databases.
4. Right-click the database that you wish to backup, point to Tasks, and then click Back Up....
5. In the Back Up Database dialog box, do the following settings:
Database: the database that you selected appears. You can change to any other database on the server from the drop-down list.
Backup type: the default type is Full. You must perform at least one full database backup before you can perform a SQL Server differential backup or a transaction log backup.
Backup component: choose Database.
Back up to: choose Disk and review the default location for the backup file (in the .../mssql/backup folder).
6. Click OK to initiate the backup. When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box. Exit SSMS.
When selecting Backup type, you can see an option called Copy-only backup. A MSSQL Copy-only backup is an independent SQL Server backup that won’t affect the overall backup and restore procedures for the database.
To do more configurations, click Media Options and Backup Options from the left panel of the Back Up Database dialog box.
Transact-SQL (T-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the SQL. You can use the BACKUP DATABASE statement to create a MSSQL backup. The basic SQL server backup database command is:
Follow the steps below to backup the complete TestDB database to disk:
1. Open SQL Server Management Studio and connect to SQL server. Then, click New Query from the Standard bar.
2. Enter the following T-SQL code snippet into the query window:
3. Click Execute to run the backup.
To put the backup file in the default backup directory, you can omit the path from a DISK clause. In my case, it would be DISK = 'TestDB.bak'.
Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name using MEDIANAME.
If you want the flexibility to do more complex operations and do not want to use T-SQL scripts, then specialized MSSQL backup software may suit you better.
AOMEI Cyber Backup is an enterprise-class backup software that enables you to backup and restore SQL databases on all Windows PCs & Servers within LAN. More specifically, you can enjoy the following benefits:
To back up MSSQL databases with less limitations, you can download the 30-day free trial, and follow the guide to have a try:
Set up the MSSQL backup software:
1. Run the installed AOMEI Cyber Backup on the server machine, navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.
2. Choose Download proxy program (or Copy link) and install it on to the device with SQL Server installed. Then, click Already installed proxy and select the device.
Next, click -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.
Create MSSQL backup task:
1. Click Backup Task -> Create New Task to launch the task creating page. Choose backup type as Microsoft SQL Backup.
2. Click on Device Name to specify the SQL instances and databases for backup. You can select one or multiple databases as you need.
3. Select Target to save backups. You can either specify a local path or network path.
4. Before Start Backup, you can also set up a Schedule to run the SQL database backup daily, weekly or monthly, and set the backup method as Full, Incremental or Differential Backup.
Execute the task. Once it's finished, you will find it in the Backup Task tab. You can edit it or restore database from it as you need.
✍More useful features:
- Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
- Email Notification enables you to receive email notifications when the task is abnormal or successful.
Wrapping things up
You have learned three effective solutions to perform MSSQL backup. You can choose SQL Server Management Studio, Transact-SQL or AOMEI Cyber Backup depending on your situation.