SQLServerCentral Article

11 Tips to Backup databases with SMO, VB, C# Powershell, Command lines

,

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 Sub

Conclusions

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.

References

Rate

4.41 (22)

You rated this post out of 5. Change rating

Share

Share

Rate

4.41 (22)

You rated this post out of 5. Change rating