How to Backup SQL Server Database

Oct 29,2018 • Filed to: Take Data Backup • Proven solutions


SQL Server is a popular Relational Database Management System (RDBMS) developed by Microsoft. It is a highly comprehensive database that was primarily designed to compete with MySQL. Even though it is a highly sophisticated DBMS, it has its own pitfalls. Therefore, just like any other database, it is required to backup SQL server database as well. In this informative post, we will make you familiar with different ways to backup your SQL server database in order to protect it from any loss.

Part 1: Why you need to backup SQL Server Database?

Also known as MS SQL, the SQL server database comes with plenty of benefits. Since it is designed by Microsoft, it has a sophisticated security and firewall that protects it against malware attack. As the database is based on SQL, it is vulnerable to various threats that are associated with it. For instance, various SQL injections can be used to abuse the vulnerability of a database management system.

Therefore, if you wish to keep your database protected, then consider taking its backup at regular intervals. SQL server is used in various organizations as a primary database. Not just renowned online publishers, it is also used by firms belonging to different domains. Nevertheless, it doesn’t matter what kind of data you store, it is of utmost importance to learn how to backup SQL server database. To make things easier for you, we have discussed two different techniques to achieve the same.

Part 2: How to Backup SQL Server Database using sqlcmd

One of the easiest ways to backup your SQL server database is by using command line interface. Although, there is a provision to take a comprehensive backup using SQL Server Management Studio. Nevertheless, if you do not have the management studio installed, then you can fulfill your needs with the assistance of “sqlcmd” command.

The "sqlcmd" command works without much trouble and provides a quick and reliable solution to backup SQL server database. Though, you would need to go to the SQL instance beforehand to make it work. After opening the command prompt, write the following command:

SqlCmd -S (SqlServerName)\SqlInstanceName

You would be required to write the respective values for the SQL server name as well as the instance name. Also, make sure that you are signed-in as Administrator while providing these commands. Else, you would have to bypass the admin privileges.

Once it is done, you can provide the following command to take a backup of your SQL server database.

SqlCmd -E -S Server_Name –Q "BACKUP DATABASE [Name_of_Database] TO DISK='X:PathToBackupLocation[Name_of_Database].bak'"

Make sure that you provide right values for the name of the database. Furthermore, the location for the backup should be clearly written in the correct syntax. Here’s an example to let you know how it is done.

SqlCmd -E -S MyServer –Q "BACKUP DATABASE [MyDB] TO DISK='D:\BackupsMyDB.bak'"

This was the command for the default SQL server instance. You can also modify it to meet your requirements for a named SQL instance in the following way:

SqlCmd -E -S MyServerMyInstance –Q "BACKUP DATABASE [MyDB] TO DISK=’D:\BackupsMyDB.bak'"

You can alter the backup location as per your needs as well. This will let you take a backup of your SQL server database to the location of your choice.

Restoring the Database

After performing a backup of your database, it is important to learn how to restore it as well. Luckily, the restoring process of SQL database is as easy as taking its backup. In order to restore it, you need to write the following command:

SqlCmd -E -S Server_Name –Q "RESTORE DATABASE [Name_of_Database] FROM DISK='X:PathToBackupFile[File_Name].bak'"

For instance, if you wish to restore the data back from "BackupsMyDB.back", then the following command would work.

SqlCmd -E -S MyServer –Q "RESTORE DATABASE [MyDB] FROM DISK='D:\BackupsMyDB.bak'"

Part 3: Backup SQL Database Using SQL Server Management Studio

With the help of SQL Server Management Studio, you can easily take a backup of your database and restore it as well without much trouble. It has a user-friendly interface that will let you manage your databases in an effective manner. If you don’t have the SQL Server Management Studio, then you can download it from its official website right here.

Once you have installed the SQL Server Management Studio, launch it and connect it to your Server > Instance. Under the "Databases" section, you can find a listing of your data. You can simply expand it to get an in-depth view.

After getting an expanded list of the databases, select the one you want to save and right-click it. Go to "Tasks" and click on the "Backup" button.

backup sql server database

This will open another pop-up window from where you can provide different values for the backup operation. Select the source database and confirm the backup type. It can be either differential or full. We recommend going with the “Full” option. Additionally, you can select the backup components from here as well.

backup sql database

Set a name for your backup, provide added description (if needed) and set an expiry date. In the end, provide a location for the backup. You can either remove the existing location or add a new one as well. Once you have made all the selections, just click on the "Ok" button.

This will initiate the backup process and make a second copy of your database. You will be notified by an on-screen message once it is completed successfully. Afterward, you can also restore your backup using SQL Server Management Studio as well.

After following these easy steps, you can simply backup SQL server database without any trouble. You can either take the assistance of command prompt or give SQL Server Management Studio a try to backup your database. Both the options can also help you restore your database too. Try not to procrastinate it and backup your database as soon as you can. This will let you avoid an unforeseen situation and you would be able to keep your data safe and protected.

AOMEI Backupper Professional - Backup Computer Data

data backup
  • Allows you to take automatic, full, incremental and differential backups of system, disk, partition and files.
  • Supports system restore, disk restore, dissimilar hardware restore, partition restore and selective file restore.
  • Supports disk clone, partition/volume clone, flexible clone, system clone and command line clone.
  • Can schedule file sync for real-time data backup to internal hard disks and external hard drives.
  • Offers additional essential utilities - PXE boot tool, merge backup images, encrypt image, etc.
  • Supports Windows 10, Windows 8.1/8, Windows 7, Vista, and XP (all editions, 32/64-bit)

Amy Dennis

staff Editor

0 Comment(s)
Home | Resources | Take Data Backup | How to Backup SQL Server Database