How to Take Database Backup MS SQL Server

In this blog you will learn how to take Full backup of a database in SQL Server 2008, 2012, 2014 and 2019 using T-SQL script. It will have on demand full backup.

Using T-SQL (Transact-SQL):

You can also use T-SQL to perform a database backup. Open a new query window in SSMS and run a query similar to the following:

BACKUP DATABASE YourDatabaseName

TO DISK = 'C:\Backup\YourDatabaseName.bak'

WITH FORMAT, NAME = 'YourDatabaseName-FullBackup',

SKIP,NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

·      Replace YourDatabaseName with the name of your database and provide the appropriate backup file path and name.

·      TO DISK: Specifies the backup file location.

·      WITH FORMAT: Overwrites existing backup files.

·      NAME: Specifies a name for the backup set.

Example:

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Backup\AdventureWorks.bak'

WITH FORMAT, NAME = 'AdventureWorks-FullBackup',

SKIP,NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

Note:

1.    Ensure you have the necessary permissions to perform a backup.

2.    Always store backups in a secure location.

3.    If the database is in use, it's a good practice to schedule backups during periods of low activity.

4.    Regularly test your backup and restore procedures to ensure data recoverability.

You can also customize your backup strategy based on your requirements, such as differential or transaction log backups for incremental backups. Always refer to the SQL Server documentation or your organization's backup policies for best practices and recommendations.

Using SQL Server Management Studio (SSMS):

1. Open SQL Server Management Studio.
2. Connect to the SQL Server instance.
3. In the Object Explorer, right-click on the database you want to back up.
4. Select "Tasks" > "Back Up...".

    5.In the "Back Up Database" dialog, specify the backup options, including the backup destination, backup type (Full), and other settings.

      6.Go to Second Tab Media Option Choose Overwrite all existing backup sets

      7. Then Go to Backup option, Select Compress backup

      8. Click "OK" to start the backup.