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.

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

1. Back Up Database Using C#

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

//references
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended
Microsoft.SqlServer.SqlEnum

//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 microsoft.sqlserver.management.smo.database, not as System.entity.database )

sqlBackup.Initialize = true;

sqlBackup.Checksum = true;

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

sqlBackup.Devices.Add(deviceItem);

//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. 
sqlBackup.SqlBackup(sqlServer);

//Remove the backup device from the Backup object.           
sqlBackup.Devices.Remove(deviceItem);

}

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

2. Backup Database By Generating Scripts using SQL Server

1. Open your SQL Server

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

3. Go to Taks->Generate Scripts

Backup-Database-using-SQL-server.png

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

Database-Backup-SqlServer.png

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

Final-Screen-Sql-database-Backup.png

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 @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
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   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

You can change the file location.

Note:
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' } |
Backup-SqlDatabase

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"};

Note:
This method is not tested by me, but it should work as per other developers


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.