Home » How To » How to Backup SQL Server Database

How to Backup SQL Server Database

SQL Server is a relational database management system developed by Microsoft. It provides a powerful graphical interface called SQL Server Management Studio (SSMS), which provides all the features to manage your databases. Also provides options to backup and restore the database using graphical interfaces. Also, we can execute the T-SQL statement through SSMS to take a backup of the database.

How to Backup SQL Server Database

We can backup the SQL Serer database either with the T-SQL statements or we can use the SSMS wizard process to take full, differential, or transactional backup of the database.

Use one of the below options to back up the SQL Server database:

1. Backup Database using T-SQL

Launch SQL Server Management Studio and connect to the database server. Now open a query window and execute the following statement to back up the database in your local drive.

BACKUP DATABSAE [Test_db] TO DISK = 'D:\backup\Test_db.bak'

Here Test_db is the database name and D:\backup\Test_db.bak is the backup file name with full path.

Related:  How to Connect your Local SQL Server from Docker

T-SQL Statement to Backup SQL Server Database

The above screenshot shows a successful database backup in the SQL server using a T-SQL statement. The next option will help you to back up the SQL Server database with a graphical interface.

2. Backup Database using SSMS

SQL Server Management Studio provides an option to backup SQL Server databases manually. To back up a SQL server database follow the below steps.

  1. Launch Management Studio and Connect to SQL Server
  2. Under databases, Select your database to backup
  3. Right-click on database and navigate to >> Tasks >> BackupBackup SQL Server Database - Step1
  4. Under the Source option, the correct database is selected in the drop-down list. The backup type is set to Full.
  5. Under the Destination section, Remove any entry that already exists by clicking the remove button available on the right side. Then Click Add button to provide the correct backup path with filename.
  6. See the below screenshot, taking a full database backup of “Test_db” at the path D:\backup\Test_db.bak.Backup SQL Server Database - Step2
  7. Once the backup is completed successfully, you will see a message like below.Backup SQL Server Database Completed
Related:  How to Change Default MySQL Data Directory in Linux

That’s it we have successfully backed-up SQL Server Database 😎

Leave a Comment