03/11/2025
A common task for database administrators is to ensure that SQL Server databases
are regularly backed up and can be restored in case of data loss or corruption.
Below are the steps to perform backup and recovery of SQL Server databases using
SQL Server Management Studio (SSMS) and T-SQL commands.
Backup SQL Server Database using SSMS:
1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.2. In the Object Explorer, expand the "Databases" node and right-click on the database you want to back up.
3. Select "Tasks" > "Back Up..." from the context menu.
4. In the "Back Up Database" dialog, choose the backup type (Full, Differential, or Transaction Log).
5. Specify the destination for the backup file by clicking "Add..." and selecting a location.
6. Click "OK" to start the backup process.
7. Once the backup is complete, a confirmation message will appear.
Restore SQL Server Database using SSMS:
1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.2. In the Object Explorer, right-click on the "Databases" node and select "Restore Database..." from the context menu.
3. In the "Restore Database" dialog, select the source of the backup (From device or From database).
4. If restoring from a device, click "Add..." to locate and select the backup file.
5. Choose the destination database name (you can restore to the original database or a new one).
6. In the "Options" page, you can choose to overwrite the existing database if necessary.
7. Click "OK" to start the restore process.
8. Once the restore is complete, a confirmation message will appear.
Backup SQL Server Database using T-SQL:
To perform a full database backup using T-SQL, you can use the following command:
BACKUP DATABASE [YourDatabaseName]
TO DISK = 'C:\Backup\YourDatabaseName.bak'
WITH INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10;
Restore SQL Server Database using T-SQL:
To restore a database using T-SQL, you can use the following command:
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'C:\Backup\YourDatabaseName.bak'
WITH REPLACE, STATS = 10;
;