• EN
Solved

Veeam SQL Server Restores

J
Userlevel 2

We want to restore a SQL Server database within a small window, so that there is minimal downtime for transferring usage from the old database to the new database. With our previous backup software (Backup Exec), we used to restore the full backup and leave the database in recovery mode, then restore the differential backup on top of that, still leaving the database in recovery mode, then restore the transaction logs up to just before the downtime period, still leaving the database in recovery mode, so that, once the downtime commenced, we would only have to restore a couple more transaction log backups to bring the database up-to-date with the source database.

We are struggling to find a similar option in Veeam. We have 2 support calls open, one attempting to see if we can use a similar method to the above, and one attempting to see if Instant Recovery is the solution to this.

- for the first option, it appears that Veeam cannot append any restores to the original restore - it simply offers to overwrite the database.
- for the second option (Instant Recovery), when I have tested this, it fails with an error "Database restore failed: Cannot drop the database 'DATABASENAME' because it is being used for replication." The source database does have SQL Server replication enabled on it.

Whilst we are able to do restores which do not involve Instant Recovery, or any attempt to "layer" restores one on the other, these can take hours in the case of databases which are a few TB in size, and this isn't an acceptable option for a planned migration of a database with minimal downtime for the users. Can anyone offer any assistance, or suggestions for an alternative method for our needs?

Thanks in advance.

 

icon

Best answer by MicoolPaul 29 October 2021, 09:07

View original

11 comments

marcofabbri
Userlevel 7
Badge +13

Try look here if can help:

Veeam SQL Service

Restore Wizard

Demo of SQL Restore

Backups are like pizza, I love them. | Linkedin: @marco-fabbri-it
J
Userlevel 2

Try look here if can help:

Veeam SQL Service

Restore Wizard

Demo of SQL Restore

 

Thanks for that. We have successfully done SQL Server database restores before, with no Instant Recovery, or any attempt at “layering” fulls & logs.

 

I’ve checked for “Veeam.SQL.Service” files on the target server and none appear to have been created during the restore attempts.

Chris.Childerhose
Userlevel 7
Badge +20

I will try to do a SQL restore but have not done many using Veeam recently.  Couple more resources to check for things like job settings, SQL versions, etc.

Planning and Preparation - Veeam Backup Explorers Guide

SQL Server backup methods in conjunction with Veeam

How to restore Microsoft SQL databases with Veeam Explorer for Microsoft SQL - YouTube

The YouTube videos by Veeam are very excellent.

Chris Childerhose - VMCA2022 | VMCE2023 | Veeam Vanguard 7* | Veeam Legend 3* | vExpert 5* | VCAP-DCV/VCP-DCV | Object First Ace | Cisco Champion | Twitter: @cchilderhose | Blog Site – https://just-virtualization.tech
wolff.mateus
Userlevel 7
Badge +11

I will try to do a SQL restore but have not done many using Veeam recently.  Couple more resources to check for things like job settings, SQL versions, etc.

Planning and Preparation - Veeam Backup Explorers Guide

SQL Server backup methods in conjunction with Veeam

How to restore Microsoft SQL databases with Veeam Explorer for Microsoft SQL - YouTube

The YouTube videos by Veeam are very excellent.

Excellent links @Chris.Childerhose. I didn’t know this document of Tibor Karaszi.

Thanks for that!

IT Lover | Veeam Vanguard | VUG Leader | Blogger
J
Userlevel 2

Hi all. Chris - thanks for the links, I had a look at those, but none of them seem to mention the restore of SQL Server databases with replication on.

I received a reply from Veeam support which said that Veeam simply cannot do an instant recovery of a database which has SQL Server replication enabled. I haven’t read about this limitation before. Can anyone confirm this fact? Or point me to any documentation which states this limitation?

Thanks!

Chris.Childerhose
Userlevel 7
Badge +20

Hi all. Chris - thanks for the links, I had a look at those, but none of them seem to mention the restore of SQL Server databases with replication on.

I received a reply from Veeam support which said that Veeam simply cannot do an instant recovery of a database which has SQL Server replication enabled. I haven’t read about this limitation before. Can anyone confirm this fact? Or point me to any documentation which states this limitation?

Thanks!

Here is confirmation of Veeam Support statement as you need the Agent.  VBR cannot do it.

Microsoft SQL Server Restore - Veeam Backup & Replication Best Practice Guide

Failover Cluster Support - Veeam Agent Management Guide

Chris Childerhose - VMCA2022 | VMCE2023 | Veeam Vanguard 7* | Veeam Legend 3* | vExpert 5* | VCAP-DCV/VCP-DCV | Object First Ace | Cisco Champion | Twitter: @cchilderhose | Blog Site – https://just-virtualization.tech
J
Userlevel 2

Hi all. Chris - thanks for the links, I had a look at those, but none of them seem to mention the restore of SQL Server databases with replication on.

I received a reply from Veeam support which said that Veeam simply cannot do an instant recovery of a database which has SQL Server replication enabled. I haven’t read about this limitation before. Can anyone confirm this fact? Or point me to any documentation which states this limitation?

Thanks!

Here is confirmation of Veeam Support statement as you need the Agent.  VBR cannot do it.

Microsoft SQL Server Restore - Veeam Backup & Replication Best Practice Guide

Failover Cluster Support - Veeam Agent Management Guide



But those mention failover clusters. That’s a different scenario to a SQL Server database using replication. Failover clusters replicate the whole database between servers. SQL Server replication uses publications and subscriptions within SQL Server to replicate a certain subset of data to another location - a totally separate database, not a replica of the same database.

J
Userlevel 2

Veeam support have now come back to me and said that there is no documentation to state that SQL replication is not supported for Instant Recovery. This seems rather an important omission to me. Does anyone have experience of backing up/restoring databases with SQL replication enabled, and is there an alternative way to do a quick restore for a large database if you can’t use Instant Recovery?

MicoolPaul
Userlevel 7
Badge +20

Morning,

 

I may be able to help with this, the key complexity as you’ve quite rightly highlighted is the replication piece. As restores would tend to break replication.

 

Can you supply more information on this attempt at moving to a new database? Is this on the same SQL database and instance? What’s the purpose behind the restore? Effectively I’m just trying to understand if this is the best way of doing things.

 

Veeam’s SQL explorers give you many powerful tools to restore specific tables, stored procs etc so you can be extremely granular if need be. When Veeam restores a backup of a SQL a database, if you have SQL transaction log shipping enabled you can have Veeam automatically roll up the transaction logs, I imagine if you’re using transaction logs for your replication then this won’t be an option to you as you need Veeam to be truncating the transaction logs to perform log shipping (more information: https://helpcenter.veeam.com/docs/backup/vsphere/backup_job_vss_sql_vm.html?ver=110)

 

More specific information would be beneficial here, I’m thinking if you did have Veeam truncating your transaction logs when it’s being backed up, there’s no reason why you couldn’t do an Instant SQL recovery to the server, then put it into the recovery state and roll up the transaction logs. Another option could be using Instant Disk Recovery to mount the disk with your production MDFs and rolling the transaction logs against that (assuming they’re on separate disks).

 

Let us know the scenario you’re protecting here such as migration to new version of SQL Server, new virtual machine, patching application that amends database structure etc and we can help more 🙂

Michael Paul - #VeeamLegend | #VeeamVanguard | Veeam Certified Architect | VMware vExpert | https://micoolpaul.com | Twitter: @MicoolPaul | Mastodon: @micoolpaul@masto.nu | Bluesky: @micoolpaul.bsky.social
J
Userlevel 2

Morning,

 

I may be able to help with this, the key complexity as you’ve quite rightly highlighted is the replication piece. As restores would tend to break replication.

 

Can you supply more information on this attempt at moving to a new database? Is this on the same SQL database and instance? What’s the purpose behind the restore? Effectively I’m just trying to understand if this is the best way of doing things.

 

Veeam’s SQL explorers give you many powerful tools to restore specific tables, stored procs etc so you can be extremely granular if need be. When Veeam restores a backup of a SQL a database, if you have SQL transaction log shipping enabled you can have Veeam automatically roll up the transaction logs, I imagine if you’re using transaction logs for your replication then this won’t be an option to you as you need Veeam to be truncating the transaction logs to perform log shipping (more information: https://helpcenter.veeam.com/docs/backup/vsphere/backup_job_vss_sql_vm.html?ver=110)

 

More specific information would be beneficial here, I’m thinking if you did have Veeam truncating your transaction logs when it’s being backed up, there’s no reason why you couldn’t do an Instant SQL recovery to the server, then put it into the recovery state and roll up the transaction logs. Another option could be using Instant Disk Recovery to mount the disk with your production MDFs and rolling the transaction logs against that (assuming they’re on separate disks).

 

Let us know the scenario you’re protecting here such as migration to new version of SQL Server, new virtual machine, patching application that amends database structure etc and we can help more 🙂

Thanks Paul!

Our scenario is that we need to migrate a database from one VM to another, with the minimal downtime for the users of the database. So, the destination is a new server, and a new instance of SQL Server. In some cases it will be the same version of SQL Server, in some cases the destination version may be newer. Our backups do truncate the transaction logs when they are backed up.

Your suggestion of an Instant Recovery to the new server, putting it in recovery state and then rolling up the transaction logs seems ideal, but when we’ve tried to restore transaction logs to a database in recovery state, Veeam simply offers to overwrite the database, not append the logs to it. If we can append the logs, that would be perfect. We wouldn’t even need to do an Instant Recovery of the database, we could do a normal restore, as we can take the time necessary to do a normal restore to the new server whilst the source database is still in use on the old server, then, at the point when we want to make the switchover, we can stop the users making changes on the old server and apply any transaction log backups taken in the interim to the new server. We just don’t know how to get Veeam to append the logs.

Thanks!

J
Userlevel 2

Hi, does anyone have any further ideas on this, based on my requirements listed above? I note that someone has marked MicoolPaul’s reply as the answer; whilst it has given us more information, it hasn’t solved our issue.

Comment


Terms & Conditions