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.
//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.
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.SqlManagementObjectsYou 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 @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_cursorYou 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-SqlDatabaseOutput:
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\BackupI 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
Buy us a coffee
Become a Patron