Introduction
Sometimes we need to create backups using code, sometimes we need to do it manually or automatically, programmatically using C#, VB, Powershell. This tutorial will show you different ways to do so.
Tip 1: The simple backup
The easiest way to create a backup is using the SQL Server Management Studio with a right click in the database and selecting Tasks>backup
You can choose the default path to backup the database and specify other backup options in the dialog that appears.
Tip 2: Generate T-SQL Code to backup automatically
To generate the T-SQL code, you need to use the Script Action to New Query Window or to file or clipboard. These options will generate the T-SQL code to backup automatically.
The code created automatically:
BACKUP DATABASE [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\test.bak' WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Tip 3: Backup using the Command line
Sometimes we need to use the command prompt to backup our database. For this purpose you can use the sqlcmd utility that comes with SQL Server. To start it, in a commany prompt run the SQLCMD command. On a local machine the utility will connect to your instance. Otherwise you may need to specify the server name like this:
Sqlcmd –S domain\instancename
To backup the database we are going to save the T-SQL of the Tip 2 in a file named sqlbackup.sql. To execute the script run this command in the cmd:
Sqlcmd –i c:\sqlbackup.sql
This command executes the .sql file and creates a backup.
Tip 4: Create a scheduled backup using jobs
The jobs let us schedule different tasks for administration and maintenance. You can schedule a backup to run every day at a specific time. The easiest way to do this is to select "Script Action to Job" in the window mentioned in Tip 1.
The job is created automatically.
And you can specify the schedule to run the backup.
Tip 5: Schedule a backup without the SQL Agent
Sometimes you only have the SQL Express edition, which does not include the SQL Agent. In this case you can use Tip 3 inside a batch file like this:
Filename:Backup.bat
Sqlcmd –i c:\sqlbackup.sql
Then you can use the Task Scheduler that comes with the Windows operating system and call the .bat file.
Tip 6: Create a backup using PowerShell
PowerShell is a pretty powerful tool that can be used to automate administrative tasks related to the operating system, the database, the web server, the mail server, etc. I love this tool to generate scripts with few lines of code.
To start PowerShell, right click on Databases in SQL Server Management Studio and select start Powershell.
We will need the sqlbackup1.sql file created in Tip 3. To invoke the SQL script with PowerShell, run this line:
Invoke-Sqlcmd -InputFile "C:\sqlbackup1.sql"
Tip 7: The Maintenance plan wizard
SQL Server includes a nice wizard to generate backups, maintain indexes, shrink databases, etc. To start the wizard, go to Management>Maintenance Plan and select the Maintenance Plan Wizard.
You can check the database integrity, shrink, reorganize indexes, etc. In this case we want to run a database backup.
You can select multiple databases to backup from this dialog.
Tip 8: The Maintenance Plan
Sometimes we need to customize a maintenance plan and the wizard is not enough for us. In this case you can create the new maintenance plan.
This tool let you create tasks and you can connect the tasks graphically. You can change the order of the tasks or run a different task in case one fails.
Tip 9: SQL Server Data Tools
In SQL Server 2008 or 2005 this application was named SQL Server Business Intelligence (BIDS), but in SQL Server 2012 it was renamed to SQL Server Data Tools because the tools are not only for Business Intelligence (SSDT)
In SSDT, you can open an Integration Service Project and then you can combine the backup with other tasks like the Send Mail, FTP tasks, Run processes. For example you can receive an email if the backup fails or upload to an FTP or FTPS the backup. You can also invoke bat files, call web services or run C# scripts.
Tip 10. ADO.net
For Windows Forms and ASP.net it is common to use ADO.net. They are used in applications to insert, delete and update data. However you can backup a database in code using ADO.net. This is a simple example on how to backup a database.
//C# code to create a backup with ADO.net
using System.Data.SqlClient;
//Create a connection to the test database. Integrated security is Windows
//Authentication
string ConnectionString = @"Data Source=Localhost;" +
"Initial Catalog=test;Integrated Security=True";
SqlConnection cnn = new SqlConnection(ConnectionString);
//backup the database test in the c:\backup folder
SqlCommand cmd = new SqlCommand(@"BACKUP DATABASE [test] TO "+
@"DISK = N'C:\backup\test.bak'", cnn);
cmd.CommandType = CommandType.Text;
cnn.Open();
//Execute the command
cmd.ExecuteNonQuery();
Console.Write("Backup completed successfully");
cnn.Close();Tip 11. SMO
SMO or SQL Management Objects is a great option used by C# and Visual Basic. This sample code shows how to backup a database using VB:
You need to add these assemblies:
- Microsoft.SqlServer.ConnectionInfo.dll
- Microsoft.SqlServer.Smo.dll
- Microsoft.SqlServer.SmoEnum.dll
- Microsoft.SqlServer.SqlEnum.dll
- Microsoft.SqlServer.Management.Sdk.Sfc.dll
Then create the code:
Imports Microsoft.SqlServer.Management.Smo
Sub Main()
'Enter the name of the database
backupmethod("test")
End Sub
'Method to make the Backup
Private Sub backupmethod(bd)
Dim Myserver As Server = New Server("localhost")
'Create the instance of the class backup
Dim backup As Backup = New Backup()
'Use windows authentication
Myserver.ConnectionContext.LoginSecure = True
Try
'Connect to the server
Myserver.ConnectionContext.Connect()
Console.WriteLine("*** Backing up ***")
Dim path As String
'Set the path of the backup
path = "C:\Testd.bak"
backup.Devices.AddDevice(path, DeviceType.File)
backup.Database = bd
'select the type of backup action (you can also make backup
'copies of records and archives)
backup.Action = BackupActionType.Database
'If the backup is not incremental set to false
backup.Incremental = False
backup.Initialize = True
backup.LogTruncation = BackupTruncateLogType.Truncate
backup.SqlBackup(Myserver)
Console.ReadKey()
'close the connection to server
Myserver.ConnectionContext.Disconnect();
Catch ex As Exception
Console.WriteLine("Connection failed.")
End Try
End SubConclusions
When you work with SQL Server you need to connect it to other applications using different methods. This tutorial shows you some different tools to create backups. Depending on the circumstances you may need a different method.
