Backup Strategies: SQL Server

SQL Server Backup Methodologies

CRITICAL: Please open a support ticket with Swizznet to notify us that you have installed a SQL database and that you would like it to be backed up. To be 100% certain, please verify this with us! If you have any questions about how to implement this please contact us. We are here to help you and we will ensure your data is protected!

Simple Database Model PlanRecommended (see next section below for information related to what a Simple Database Model is).

  1. Swizznet will run a nightly backup of your SQL databases which will truncate the logs, but you MUST run the database in Simple Recovery Model for this to work properly. If you don’t understand what a “Simple Recovery Model” is, there is an explanation at the end of this document.
  2. You should set up a disk free-space alerting method at a minimum on your SQL database drive with a monitoring system. If you do not have one, com offers a good solution for free. The goal is to have an email sent to someone which will monitor your disk utilization and can make adjustments if needed. We strongly recommend you use an email distribution group so multiple people receive the notifications.
  3. We strongly recommend you set up a Database Optimization plan . See the section below for details on how to setup a DB Optimization plan.

Note: Automated Maintenance or backup plans are not available on SQL Express.

Full Database Model Plan – The Full Model is only supported on SQL Standard Edition or higher.

Running a SQL database in Full Model mode requires that you have an advanced understand of how to manage and maintain the Database(s). There are more intricacies to using the Full Model and you must set up both a backup and maintenance plan for it to work correctly. This document shows you how to do that.

The key thing you MUST know is that under the full model YOU are responsible for setting up a backup strategy within SQL. Swizznet CANNOT directly backup your database for you! If you have questions or need assistance please reach out to us, we are here to help you!
Remember: Backups require storage so be sure you plan to have enough space. SQL system backups MUST be saved on a G:\ drive so you will need to talk to Swizznet about having this attached to your server to have sufficient storage.

_________________________________________________________________________________________

How to Setup a SQL Backup and Maintenance Plan

To setup a SQL maintenance or backup plan, you need to access the SQL Server Management Studio tools (this tool is available in SQL Express, however you cannot setup automatically scheduled jobs as shown below, but you can still run these types of jobs manually if you choose to).

The easiest way to locate the SQL Server Management Studio tool is to Search for “SQL Server” and you should see this on your screen:

 

 

Once the tool opens, click Connect. You may have to enter a username and password. If you don’t know this data please contact support for assistance.

 

 

 

 _________________________________________________________________________________________

SQL Backup Plan for Full Model Databases and Transaction Logs

When running a database in the Full Model you need to setup two types of backups:

  1. Database backup
  2. Transaction log backup

You must run a transaction log backup so that you can manage your log files. If you do not understand what is meant by this please see the bottom of this document to read about the two different SQL models.

It is critical to understand that if you do not backup your transaction logs on a regular basis they will continue to grow indefinitely and eventually your disk drive will run out of space and cause your database to stop working.

If you are running your database in Simple Recovery Model then you only need to setup a Database backup plan because the transaction logs are automatically truncated.

Inside SQL Studio, locate “Maintenance Plans” in the Object Explorer. Usually, this is in the tree structure on the left side as shown in the image below.

  • Right click it
  • Select New Maintenance Plan and give it a name
  • Click OK

 

 

Once you have created the plan you should see a tab on the right window pane with the name of “MY NEW PLAN” (#1 in the image) and this panel, “canvas”, is where you will “design” your plan. Notice the “Toolbox” (#2 in the image) on the left margin of the screen.

  • Select the Toolbox

 

  • You should see a new pane as shown in this image
  • Click, hold and drag the “Back Up Database Task” to the canvas on the right – see second image below

 

  • Drag and drop to the canvas

 

  • Once the task is on the canvas you will right-click the Backup Database Task
  • Select edit (you can also double-click it and it will open)

 

To configure the task, follow these steps as they correspond to the image below:

  1. Select backup type “Full”
  2. Choose the database you want to backup. The simplest thing is to choose All Databases, however you can be more selective and only select specific databases you want to interact with.
  3. It is recommended that you create a sub-folder for each backup – click the box
  4. Click the ellipsis to browse for a storage location
  5. The storage location MUST be on the G:\LocalSQLBackup – if this does not exist stop and create this drive and folder. If you need help or have questions contact support.
  6. Do NOT check the Verify box
  7. Press OK

Now you need to schedule your backup by clicking the calendar icons as shown in this image.

You should set up your plan to run before 11 p.m. PT or after 2 a.m. PT as Swizznet has health reboots that run between 12 a.m. – 1:30 a.m. PT which might interfere with your backups. For the backup, you want to set it to run once per day when the database is quiet and no one is using it.

If you are running in the Full Recovery Model then the next task is to follow the same steps and setup a backup for the Transaction logs.

  1. Select backup type “Transaction Log”
  2. Chose the database logs you want to backup. The simplest thing is to choose All Databases.
  3. It is recommended that you create a sub-folder for each backup – click the box
  4. Click the ellipsis to browse for a storage location
  5. The storage location MUST be on the G:\LocalSQLBackup – if this does not exist stop and create this drive and folder. If you need help contact support.
  6. Do NOT check the Verify box
  7. Press OK

Unlike a backup which should only run once per day, you can setup your transaction log plan to run throughout the day. This image shows a job running once every 4 hours.

 

  

 

If you are running in the Full Recovery Model you should now have two plans; one Backup for the databases and a backup for the Transaction logs both of which are being written to the local G:\ drive.

If you are running in the Simple Recovery Model you should only have a backup plan.

Notify Swizznet that you have created the backup plan and Swizznet will then use its backup system to grab those files for long term storage. Please be sure you work with Swizznet as this is the final step to ensuring your data is safe.

By default, Swizznet will store the SQL backup files in our 30-day retention backup vault and replicate the backups to our remote data center. If you feel you need more than this please let our sales team know.

 _________________________________________________________________________________________

Deleting Old Backup Files

  1. You should keep your local SQL backups for two or three days, but if you’d like to keep more copies for a longer period of time just be sure you plan for the amount of storage you will require.
  2. The locally stored backups are considered your “quick restore” files as they will remain stored on the local server and will be quickly available without any interaction from Swizznet.
  3. You should create a maintenance plan to automatically delete the old backup files so you are not paying for storage you do not need.

 You will create a new plan as follows:

  1. Create a new Maintenance Plan to delete the old backup files
  2. From the toolbox drag the Maintenance Cleanup Task to the design canvas #3 in the image
  3. Right click the Maintenance Cleanup task and configure as shown below

 

As shown in the image below:

  1. Select delete old backup files
  2. Select the location of the files (if you have followed the directions above these should be located at G:\LocalSQLBackup)
  3. Type “bak” as the file extension name
  4. Check the box to include first-level subfolders
  5. Check the box to delete files based on the age of the file at task run time
  6. Enter a reasonable number of days for deletion. Remember the longer you keep these files the more you will pay for storage.
  7. Click OK when you are done

 

 

As you have done previously be sure you schedule your job to run on a regular basis. Once per night is sufficient.

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.