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.
How to backup MSSQL databases in SQL Server 2012/2017/2019 easily
In this part, I’ll introduce three effective solutions to run Microsoft SQL Server backup.
Solution 1: Using SQL Server Management Studio
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. Learn how to backup a MSSQL database via SSMS in detail below:
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 differential 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. It 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.
Solution 2: Using Transact-SQL
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.
Solution 3: Using specialized MSSQL backup software
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 Centralized Backupper is an enterprise-class backup software that enables you to backup and restore all Windows PCs & Servers within LAN. The supported data includes files, partitions, disks, OS and SQL databases.
More specifically, you can enjoy the following benefits with its SQL Server Backup feature:
To backup MSSQL databases, you could download the 30-day free trial, and follow the guide to have a try:
Installation: Install ACB on a computer as the central management console, and use it to install the client program on all computers you want to manage.
Setup: Request control for the client computers with SQL instance installed. After confirmation, you can continue to create centralized backup task.
1. Navigate to Tasks > New Task > SQL Server Backup.
2. Name the task as you like, and click + Add Computers in Step 1 to detect all the client computers with SQL instance. It will return a list of results and you can select one computer from it.
3. Now in Step 2, click + Add to detect the instances on the selected computer, and choose what to backup. You can select as many databases as you want.
4. Click on Step 3, and click Add Storage to enter the network path of share of NAS as the backup target. The added path will be saved for your direct use in the future.
5. Manage the Settings or set up a Schedule as you need, then you can click Start Backup to Only create the task or Create and execute tasks.
The created tasks will be added to the Tasks tab. You can directly edit the task or restore databases from it.
Settings - Here you can configure the backup encryption, compression, or enable email notification.
Schedule - Choose a schedule type (Once/Daily/Weekly/Monthly) and set up its frequency and execution time points specifically. In Advanced tab, you can also choose full backup or differential backup. But please note you can only perform full backup on the master database.
Wrapping things up
You have learned three effective solutions to perform MSSQL backup. You can choose SQL Server Management Studio, Transact-SQL or AOMEI Centralized Backupper depending on your situation.
If you need to protect MySQL database as well, please refer to MySQL full backup for a more applicable solution.