Posts

Tips to backup & restore your SQL Server

Microsoft SQL Server is often one of the most critical applications in an organization, with too many uses to count. Due to its criticality, your SQL Server and its data should be thoroughly protected. Business operations rely on a core component like Microsoft SQL Server to manage databases and data. The importance of backing up this server and ensuring you have a recovery plan in place is tangible. People want consistent Availability of data. Any loss of critical application Availability can result in decreased productivity, lost sales, lost customer confidence and potentially loss of customers. Does your company have a recovery plan in place to protect its Microsoft SQL Server application Availability? Has this plan been thoroughly tested?

Microsoft SQL Server works on the backend of your critical applications, making it imperative to have a strategy set in place in case something happens to your server. Veeam specifically has tools to back up your SQL Server and restore it when needed. Veeam’s intuitive tool, Veeam Explorer for Microsoft SQL Server, is easy to use and doesn’t require you to be a database expert to quickly restore the database. This blog post aims to discuss using these tools and what Veeam can offer to help ensure your SQL Server databases are well protected and always available to your business.

The Basics

There are some things you should take note of when using Veeam to back up your Microsoft SQL Server. An important aspect and easy way to ensure your backup is consistent is to check that application-aware processing is enabled for the backup job. Application aware processing is Veeam’s proprietary technology based on Microsoft Volume Shadow Copy Service. This technology quiescences the applications running on the virtual machine to create a consistent view of data. This is done so there are no unfinished database transactions when a backup is performed. This technology creates a transactionally consistent backup of a running VM minimizing the potential for data loss.

Enabling Application Aware processing is just the first step, you must also consider how you want to handle the transaction logs. Veeam has different options available to help process the transaction logs. The options available are truncate logs, do not truncate logs, or backup logs periodically.

Figure 1: SQL Server Transaction logs Options

Figure 1 shows the Backup logs periodically option is selected in this scenario. This option supports any database restore operation offered through Veeam Backup & Replication. In this case, Veeam periodically will transfer transaction logs to the backup repository and store them with the SQL server VM backup, truncating logs on the original VM. Make sure you have set the recovery model for the required SQL Server database to full or bulk-logged.

If you decide you do not want to truncate logs, Veeam will preserve the logs. This option puts the control into the database administrator’s hands, allowing them to take care of the database logs. The other alternative is to truncate logs, this selection allows Veeam to perform a database restore to the state of the latest restore point. To read more about backing up transaction logs check out this blog post.

Data recovery

Veeam Explorer for Microsoft SQL Server delivers consistent application Availability through the different restore options it offers to you. These include the ability to restore a database to a specific point in time, restore a database to the same or different server, restore it back to its original location or export to a specified location. Other options include performing restores of multiple databases at once, the ability to perform a table-level recovery or running transaction log replay to perform quick point-in-time restores.

Figure 2: Veeam Explorer for Microsoft SQL Server

Recovery is the most important aspect of data Availability. SQL Transaction log backup allows you to back up your transaction logs on a regular basis meeting recovery point objectives (RPOs). This provides not only database recovery options, but also point-in-time database recovery. Transaction-level recovery saves you from a bad transaction such as a table drop, or a mass delete of records. This functionality allows you to do a restore to a point in time right before the bad transaction had occurred, for minimal data loss.

And it is available for FREE!

Veeam offers a variety of free products and Veeam Explorer for Microsoft SQL Server is one that is included in that bunch. If you are using Veeam Backup Free Edition already, you currently have this Explorer available to you. The free version allows you to view database information, export a database and export a database schema or data. If you’re interested in learning more about what you get with Veeam Backup Free Edition, be sure to download this HitchHikers Guide.

 

This article was provided by our service partner : veeam.com

veeam

Getting started with Veeam Explorer for Microsoft SQL Server

Believe it or not, I used to work a lot with Microsoft SQL Server. While I did not call myself a database administrator (DBA), I did know my way around a database or two. Since I’ve been at Veeam, I have always enjoyed telling the Veeam story around using SQL Server as a critical application that needs the best Availability options.

That’s why I took particular interest in Veeam Explorer for Microsoft SQL Server that came in Veeam Backup & Replication. Veeam Explorer for Microsoft SQL Server allows application-specific restores of SQL databases, and also contents of tables, objects such as stored procedures, views and more. Additionally, you can also restore the databases to a specific transaction.

This is a great combination of functionality from the established application-aware image processing with a dedicated tool for database restores in Veeam Explorer for Microsoft SQL Server. Additionally, Veeam Backup & Replication and the Veeam Agent for Microsoft Windows also provide an image backup of the entire system.

For those who are not a DBA, sometimes dealing with low-level SQL Server topics can be a bit overwhelming. To help this process, I created a few scripts to help individuals learn this type of interaction with SQL Server. I put three (and a deleted script) up on the Veeam Github site. To use this script, only an S:\ drive is needed (the path can be changed) to create the sample database and put in a SQL Server Agent job to automatically run a few stored procedures that will insert and delete random data. This creates a database called SQLGREENDREAM.

After running the three scripts to create the database, implement the random number function and set the schedule to create the random data (2 records) and delete 1 record. The SQL Server Transaction Log Backup will show the new database being backed up after the next incremental backup:

Veeam Explorer for Microsoft SQL Server

Once the interval of the SQL Server Agent job runs (12 minutes in the GitHub script) and the Veeam Backup Job interval passes, the most selective restore point option can be selected in Veeam Explorer for Microsoft SQL Server. This selective option, to restore to a specific transaction, is shown in the figure below:

Veeam Explorer for Microsoft SQL Server

Once the interval of the SQL Server Agent job runs (12 minutes in the GitHub script) and the Veeam Backup copy interval process through a time when the test data has been run, the restore to a specific transaction option can be visible to the controlled scripting for the SQLGREENDREAM database in the GitHub repository. Then you can see the records in question being just as scripted, 2 records added then one record deleted. Those entries are done by the SQL Server Agent:

From there, the restores can be done with confidence to see how the SQL databases are restored with Veeam. With the sample scripts in the GitHub repository, one can become more comfortable with these restore situations when venturing out of normal comfort zones! If you are using Veeam Backup Free Edition and the SQL Server is a VM being backed up, you can still use Veeam Explorer for Microsoft SQL Server to restore the database to the time of the image-based backup; just no transaction rollback. You can use the NFR program for a fully functional installation also.

This article was reposted from Veeam.com