SQL Server: Backup and Restore Database using T-SQL

Its very easy to take Backup and Restore your Database using T-SQL even if you are not a DBA. In one of the client application, I wanted to take periodic Backup without logging in to SQL Server Management Studio. Here in this small tutorial I will try to elaborate the procedure of taking database backup using T-SQL.

1. Create a Database

CREATE DATABASE PracticeDB

2. Take Backup of the Database

BACKUP DATABASE PracticeDB
TO DISK='C:\PracticeD.bak'
WITH DESCRIPTION='Database Backup',
INIT,
PASSWORD='TryAgain'

(We can use many Options with 'WITH' in the above query)

Description is the free text used to describe the Backup.

INIT Overwrites the Backup with the same name. If we dont use INIT the backup will be appended to the previous backup if any present.

PASSWORD is used to keep secure your Backup. On Restore you will be prompt to enter this password.

Now you have sucessfully taken Backup of the Database

3. Restore the database from the previously taken backup

RESTORE DATABASE practiceDB
FROM DISK='C:\PracticeD.bak'
WITH PASSWORD='TryAgain'