Skip to Content
Technical Articles
Author's profile photo KENAN JADDENE
KENAN JADDENE
November 15, 2019 3 minute read

Scheduled Automatic SQL Database Backup using SSMS

These are the following steps you need to follow in order to create a .bak file (backup) using SSMS:
To automate and schedule a backup with SQL Server Agent:

Open your SQL server Management Studio

Log into SQL Server Management Studio (SSMS) and connect to the database. Go to the Object Explorer window (located on the left) and make sure that your SQL Server Agent is running.

Open the folder named Management and find the Maintenance Plan folder. Then right-click on Maintenance Plan folder and select New Maintenance Plan. How to Backup SQL Database Automatically

Enter the name of the Maintenance Plan you are going to create. Press on the calendar icon on the top-right highlighted section on the job schedule screen. It will bring up the job schedule screen. In the opened window set up all necessary backup schedule.

Now choose from the left-side window, select the Backup Database Task to set up the backup process and drag the element onto the right window as shown in the picture.

Now you need double-click on the database task. In the opened window set up, the configuration related to the database backup.

Specify the databases you are going to backup. Also, you can pick the files to expire after specific period or on a specific date.

Now from the Object Explorer > SQL Server Agent > Jobs
under the SQL Agent jobs, we may find the job that created automatically according to the Maintenance Plan that we created.

And right-click on this job, and press Start job at Step, to test the maintenance plan that we created before,

Now let’s go to the backup file that we defined previously to check the backup,

SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take in consideration a different approach to back up your databases when you use these editions.

Currently SQL Server Express users can back up their databases by using one of the following methods:

  • Use SQL Server Management Studio Express. This is installed together with either SQL Server Express Advanced Service or SQL Server Express Toolkit.
  • Use a Transact-SQL script that uses the BACKUP DATABASE family of commands.

Maintenance Plans are more suitable for less experienced DBAs because they provide an easy to use GUI, and do not require manually written maintenance scripts. The downside of Maintenance Plans is that the tasks that Maintenance Plans provide are basic and don’t leave space for customization.

A Maintenance Plan is also atomic and is therefore not able to run multiple tasks. Each type of maintenance task within a single Maintenance Plan can only be configured to run once within that Plan. For example, if a task that is made to delete older backup files it will only delete one file type at a time. Because of this multiple Maintenance Plans have to be created just to perform a single task, in some cases, and every Maintenance Plan has to have a corresponding SQL Server Agent job to be scheduled.

That is all, your backup plan is ready, and all your backups will be made according to the specified schedule. Now you know how to backup SQL database automatically.

 

Hope this helps,

Kenan JADDENE

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo S Sriram
      S Sriram

      Hi Kenan.

      Good one.

      Regards

      SS

      Author's profile photo KENAN JADDENE
      KENAN JADDENE
      Blog Post Author

      Thank you Sriram

      Author's profile photo Diego Hernandez
      Diego Hernandez

      Good job, thankU

      Author's profile photo KENAN JADDENE
      KENAN JADDENE
      Blog Post Author

      Thank you too Diego,

      Author's profile photo Vikas lalwani
      Vikas lalwani

      Good article to schedule database back up using SSMS.

      If someone want's to back up using C#

      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);

}

      Source

      Then you can call create a job using Hangfire.IO to create a background job to run it in background automatically for every day or week.

      Author's profile photo Amanda shankle-knowlton
      Amanda shankle-knowlton

      This blog post had EVERYTHING I needed to set up a backup task that I've needed to do for a while. Good job and thanks!

      Kenan, I hope you are paid well and that your company treats you like a rockstar!

      Fun fact - I created a login on this page for the sole purpose of being able to comment and say thanks!