It is always necessary to save or back up your database regularly so that we don't have to do database work from scratch if anythings happens to original data, so in this post I will show how to take SQL server database backup using SQL Server Management Studio or C# or to take backup of all databases of SQL server using simple script.

Various ways to back up SQL server database

Let's take a look at some possible ways to take backup of the database(SQL Server):

1. SQL Server backup database using C#

Yes, it is possible to create a backup of the database using C# code, here is the code for it.


//Method, pass your databaseName, username, password, server name and destination path to save backup file
public void BackupDatabase(string databaseName, string userName, string password, string serverName, string destinationPath)

{            //Define a Backup object variable.

Backup sqlBackup = new Backup();
//Specify the type of backup, the description, the name, and the database to be backed up.

sqlBackup.Action = BackupActionType.Database;

sqlBackup.BackupSetDescription = "BackUp of:" + databaseName + "on" +DateTime.Now.ToShortDateString();

sqlBackup.BackupSetName = "FullBackUp";

sqlBackup.Database = databaseName;
//Declare a BackupDeviceItem

BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath + "FullBackUp.bak", DeviceType.File);

//Define Server connection

ServerConnection connection = new ServerConnection(serverName, userName, password);            //To Avoid TimeOut Exception

Server sqlServer = new Server(connection);          
sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
Database db = sqlServer.Databases[databaseName]; (Reference Database As, not as System.entity.database )

sqlBackup.Initialize = true;

sqlBackup.Checksum = true;

sqlBackup.ContinueAfterError = true;
//Add the device to the Backup object.


//Set the Incremental property to False to specify that this is a full database backup. 
 sqlBackup.Incremental = false;
sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);

//Specify that the log must be truncated after the backup is complete.        
sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
sqlBackup.FormatMedia = false;

//Run SqlBackup to perform the full database backup on the instance of SQL Server. 

//Remove the backup device from the Backup object.           


As you can see in the above method created using C#, you need to pass all the details of your database like Server Name, User Name, Password, Destination path etc.

You might also need to install a Nuget Package for using above code, so in your Visual Studio, go to Tools->Nuget package Manager -> Package Manager Console and install the package "Microsoft.SqlServer.SqlManagementObjects"

Install-Package Microsoft.SqlServer.SqlManagementObjects

You might also like to read

Restore (Import) database from .bak file in SQL server (With & without scripts)

2. SQL Server Backup By Generating Scripts in SQL Server

1. Open your SQL Server

2. Right Click on Database(which you want to backup)

3. Go to Taks->Generate Scripts


4. Click on generate scripts and then Choose  from "Script entire database and all database objects" if you want to take complete database backup Or Choose "Select Specific database objects" if you want to select objects manually, here in this example we are going to take complete database backup, so we are selecting option 1.

5. Click "Next", select location and give name to backup script


Click on "Advanced" if you want to change details like destination database version, include Indexes etc.

5. Click "Next" and Review your backup scripts details

6. Click "Next" to confirm, and SQL Server will start generating script

7. Finally, you will see output like this


You can check generated script by opening the file location, which we had selected during Step 5.


3. Take Backup of all databases of SQL Server

You can use the below script that will allow you to backup each database within your instance of SQL Server.

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'C:\Backup\'  
-- specify filename format
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
   FETCH NEXT FROM db_cursor INTO @name   

CLOSE db_cursor   
DEALLOCATE db_cursor

You can change the file location.

In the above script we are bypassing the System database, we can include it if needed

You can create Windows task scheduler to run this script automatically after a certain duration of time using cmd prompt.

4. Backup all databases Using Powershell

Yes you have read that right you can easily backup a complete instance of SQL Server using PowerShell command

Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } |



By default the backup file is stored in the default server backup location under the name databasename.bak for full and/or file backups and under the name databasename.trn for log backups.

Example, when I ran above power shell script, database backup (.bak) was saved here

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup

I am using SQL Server express 2019 edition. (as location can change according to your database edition)

If you need to add a few more options, you can do something like this.

<# Backup All Databases and give them all a file name which
    includes the name of the database & datetime stamp. #>
Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } | foreach{$_ |
Backup-SqlDatabase -CompressionOption On -BackupFile "$($_.NAME)_db_$(Get-Date -UFormat %Y%m%d%H%M).bak"};

But compression is not supported in Powershell express edition.

Here is the Video which shows how to take SQL server database backup using SSMS and how we can restore database using this scripts.

That's it, these were the possible ways to backup SQL Server database, share your views using Facebook comment's section or Login to comment, up vote, down vote this article, thank you.

You may also like to read:

Restore (Import) database from .bak file in SQL server (With & without scripts)

Download and Restore AdventureWorks Database

Import CSV in SQL Server Database

Download and Install SQL Server

Download and install SSMS