Configure Microsoft SQL Server Backup

MS SQL Server Backup

The default instance and named instances of SQL Server 2000, SQL Server 2005 and SQL Server 2008 will be auto-detected and listed for backup. However, in some cases, MSDE and SQL Server Express edition instances will not be listed automatically for backup. This section gives you information on how to add the MS SQL Server instances for backup which were not listed automatically in the Add SQL Server Backup page.

Requirements

• MS SQL Server 2000 / MS SQL Server 2005 / MS SQL Server 2008
• Vembu OnlineBackup
• To back up the MS SQL Server database(s) you need to install OnlineBackup on the machine running Microsoft SQL Server

It is highly recommended to include all the system and user databases in the SQL Server database backup schedules. In the case of complete system failure, it will be necessary to restore the system databases along with user databases in order to recreate the SQL Server environment.

System database backup enables rebuilding the system in case of a system or database failure. The master databases should be backed up separately from other system databases as only full backups of master databases are allowed.

• The SQL Server system database(s) are master, msdb, model and tempdb. It is important to have regular backups of these system databases except tempdb.
• Since tempdb is rebuilt each time when a SQL server restarts and is deleted permanently when the server shuts down, it is not required to be backed up.
• And Model database needs to be backed up, only if customized.

Step 1- Select MS SQL Server

• From the Backup tab, click the MS SQL Server option to begin the backup process. Select a SQL Server Instance name (i.e MS SQL instance which is to be backed up)

• Select a SQL Server Instance Name (i.e MS SQL instance which is to be backed up) from the SQL Server Instance Name drop-down list. You can add SQL Server Instance from the Add SQL Server Instance option. You can add the MS SQL Server instance name which is not listed for backup. The second table contains a list of automatically detected and/or manually added SQL Server instances in this machine.
• Once you have provided the Instance Name, click the Add SQL Server Instance option. To know more about this page, click the About this page option available at the top of the page.

• Click Configure MSSQL Backups option to proceed with the backup process.

• From the list of SQL Server Instances, choose the required Instance. If you have changed the credentials for that particular Instance, click No option and provide the necessary credentials. If not, proceed with Yes.
• If Yes- Proceed to Configure Database. This option will proceed with the previous authentication which was already provided.

• If No- Provide new authentication credentials based on choice:
• If Windows authentication is selected click Next
• If MS SQL authentication is selected, provide user credentials for that instance and click Next

Step 2: Choose Database

• A page that lists selected MS SQL Server database is shown. SQL Server databases are classified into the following models:
• Full Recovery Model
• Bulk-Logged Recovery Model
• Simple Recovery Model

When you select a particular Recovery Model, the databases of that Recovery Model type will be selected from the database list for backup.

• Select All Databases for this schedule

If you select this option, 'all' the databases present in the SQL Server will be selected. If a new database is added after this, it will be automatically detected and added to the SQL Server. It will be further added to this schedule also.

Full Recovery Model

The Full Recovery Model provides extensive backup and restore capabilities. The full Recovery Model does not require any log backups and can be recovered only to a certain extent. Full, differential and Transaction Log backup of the databases are supported. If you want to restore a Full Recovery Model database, a full backup has to be restored first. On top of the full backup, Differential and/or Transaction Log backups could be restored to recover the database to the latest backup state.

• Select All Full Recovery Model databases

• "Select All Full Recovery Model databases" will include all the databases with the Full Recovery model from the SQL Server to the backup schedule. If a new Full Recovery model database is later added to SQL Server, it will be automatically identified and included in this schedule.

Simple Recovery Model

The Simple Recovery Model offers one of the simplest forms of backup and restore. Full or differential backup of the databases could be done. Transaction Log Backups are not available for databases belonging to this model. For recovering a Simple Recovery Model database, a full backup has to be first restored. On top of the full backup, Differential Backups could be restored to recover the database to the latest backup state.

• Select All Simple Recovery Model databases

• "Select All Simple Recovery Model databases" will include all the databases of the Simple Recovery Model from the SQL Server. If a new Simple Recovery model database is added to SQL Server instance after this then it will be automatically detected and included in the backup schedule.

Bulk Logged Model

• Similar to the Full Recovery Model database, Bulk Logged Model databases provide extensive backup and restore capabilities. Full, Differential and Transaction Log backup of the databases is supported. However, when compared to the Full Recovery Model that logs all transactions, this model minimally logs bulk operations. Recovery is possible only up to the end of a Transaction Log Backup.
• Like the Full Recovery Model database, to restore a Bulk Logged Model database, a full backup has to be first restored. On top of the full backup, Differential and/or Transaction Log Backups could be restored to recover the database to the latest backup state.

• Select All Bulk-Logged Recovery Model databases:

• "Select All Bulk-Logged Recovery Model databases" option will include all the databases of the Bulk-Logged Recovery Model type from the SQL Server. If a new Bulk-Logged recovery model database is added to SQL Server post this, it will be automatically detected and included in the backup schedule.

• Let me manually choose databases:

• This option allows you to select the databases you wish to backup irrespective of the recovery model. If a database does not support Differential/Transaction Log backups, then that Differential/Transaction Log backup will be skipped for that database and the error message will be reported in the backup report.
• Select any database and proceed further by selecting Next.

• Select the appropriate option required for database selection. If you are selecting a database in a backup schedule, that database cannot be included in another backup schedule as the data might get inconsistent when backed up in two different schedules. Once done, click Next

Step 3: Configure  Full Backup Scheduling

• Configuring a full backup of your MS SQL Server data is a must and cannot be omitted. A full backup is necessary because OnlineBackup will directly stream your MS SQL Server database(s) when a full backup is configured and will transfer the data directly to Vembu Cloud. Once the full backup is completed, the log files that are older than the full backup starting time will be purged.

• Users can configure their backup schedules flexibly based on their requirements. They can choose from Hourly/Daily/Weekly options for backup schedules.

• Run Once- This option will run full backup only once
• Run Daily- This option will run a full backup once in a day
• Run Weekly- This option will run a full backup once a week. User can select weekday and time on which the full backup should run
• Run Monthly- This option will run a full backup once a month. Users can select the time, day and month during when the backup should run. This is the default and recommended configuration

Full Backup Retention

• Maximum Number Of Full Backups:

You can specify the count for the maximum number of full backups to be retained. For example, when you set the count as 2 for retaining the full backups in the backup server, only the latest 2 versions of the full backup will be maintained at all times. The default value is 2 for full backup retention. Retention count runs from '01' to '99'.

Once done with scheduling full backups, click Next to proceed to configure Additional Scheduling.

Step 4- Configure additional scheduling

There are two backup types available that are optional and can be disabled if required:

• Differential backup
• Transaction log

Based on requirements edit these additional settings, but make sure this additional scheduling stays less than full backup schedules.

Differential- OnlineBackup will do a Differential Backup dump of the database with respect to the previous full backup whenever this Differential Backup is scheduled. Configuring this step is optional

Transactional Log- OnlineBackup will do a Transaction Log Backup dump of the database with respect to the previous backup (Full/Differential/Transaction Log) whenever this Transaction Log Backup is scheduled. Configuring this step is optional

Scheduling

• Run Weekly- This option lets the user select the day and time of a week when to run the scheduled backup. Users can also select more than a day in a week for a schedule.
• Run Daily- This option lets the user provide a specific time to trigger backup on a daily basis.
• Run Every Few Hours- This option lets the user decide the time period in which backup schedules should be carried out. Additionally, it allows the user to configure days in a week and decide when to and when not to backup.

Add Mapped Drive

• There may be situations in which you may not have enough space to allocate for the dump process. In such cases, you can add a mapped drive by providing the drive name, drive path, username, and password. From there on the dumping process will happen to the configured drive only.
• The drive path must be in the following format: \\<MACHINE_NAME OR IP_ADDRESS>\<SHARE_NAME> make sure you give the correct details else you will get the status "Mapping the drive letter to the UNC path failed with the error: The network path was not found."
• Ensure you provide proper credentials, if not the message "Mapping the drive letter to the UNC path failed with the error: The specified network password is not correct" will appear. Click Save to complete the process.

Temporary location for backup dumps

Enter the location/directory path in which the MS SQL dumps have to be temporarily stored, before uploading to the backup server. Ensure you have enough disk space in the configured location(where the backup dump has to be saved).

Encryption Setting

Backups configured under OnlineBackup can be encrypted with a user-defined password which can be provided during backup configuration.

Steps to add Encryption Password

• In order to encrypt your backup, select Enable Encryption checkbox. You can either add your own password or encrypt the backup with a System Generated Password.

• To create a new password, click on Add Password. Provide the password of your choice along with a password hint and save it

• The newly created password will be available in the drop-down list. Choose the password and click Next. All the added passwords will be listed in the drop-down box. With the help of the encryption hint, you can easily identify the required password. Click Save once you have provided the required details.

• Once you have completed configuring Encryption Password, click Save.

Step 5- Run Backup

• The last step in the MS SQL Server backup configuration is to enter the backup schedule name. OnlineBackup supports creating multiple backup schedules each with its own configuration. The backup schedule name will uniquely identify the backup. While restoring your backup data, you need to choose the data to be restored using its schedule name.
• Review the configuration provided and enter an appropriate name for the backup schedule. Once done, click Save the Backup to initiate the backup progress as per schedule configured and it gets completed successfully. Select Run this backup immediately after saving this configuration option. This will trigger the backup once you save your configurations.

• You can verify the progress of your backup in the progress schedule window. In the List Jobs page, click on the arrow mark next to your backup schedule to open the backup progress window. The overall progress of your backup along with details such as :
• Job Name - Name of your backup job
• Backup Server - The backup server to which the backup is currently happening
• Warning - Warning if any
• Reconnection Attempts - The count of the number of times OnlineBackup client attempted to reconnect to the backup server
• Transfer Rate - Rate at which the backup data is transferred to your backup server. (Bytes,KB,MB,GB)
• Original Size of the file - The actual size of your backup file
• Added, Modified, Deleted, Skipped files and Folders count
• Time Left - Time remaining for your backup to complete.

• You can abort your backup if not required from the Abort option available in the List Jobs page. You will get a pop up as shown below. Click Yes to Abort your backup.

• You can Abort your backup from the backup progress window page also. Select the Abort (-) option in that window. You will be alerted with a pop-up as shown below. Click OK to abort your backup.