Backup Thread 2: Test the restore procedure!

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > Backup Thread 2: Test the restore procedure!

Panthrax: Jul 12, 2001; I'm gonna hit you until candy comes out.

Hi thread, I just found this, and I have a backup problem. Please help me! It's a little complicated, at least in my head, so I'll try to dump whatever I can.

We're moving from EMC Networker to Veeam, and we have a third-party DBA that does all of the MS SQL work and takes backups via SQL directly and copies them off for us to backup through other means. Before, Networker was simply set to exclude any SQL-related files, and back up everything else, which seemed to work just fine. Now, with Veeam, it's doing a whole lot more stuff that we're trying to either get it to stop doing, or explain to the DBAs that this is how it works and it's not actually breaking the SQL backups. Note that these are physical servers, not VMs.

We have the agents set to not do application aware backups, so it shouldn't be messing with SQL. We're seeing Veeam stop VSS writers and pause SQL for the backups, but we're also seeing Veeam try to log into SQL server with the veeam backup user account, which fails because it's not an allowed account in SQL server, so it then tries to do it via NT AUTHORITY\SYSTEM, which succeeds and it takes a backup? I guess? of a few of the DBs (maybe just the master DB) then exits. It seems to be doing this via a WITH COPY_ONLY option which seems to not break the backup chains, but it's a real pain to have to deal with for the DBA so they don't have to verify their own backups, make sure logs weren't truncated, etc. Their responsibility is to make sure the SQL backups are good and can be restored, while our responsibility is to get the server, OS, etc back up and running.

My question is, can we get Veeam to stop messing with SQL? It's fine if it needs to stop VSS to do its thing, but logging into SQL is causing all kinds of headaches for everyone. I just want to back up the C: drive and some random files on other volumes, that may or may not have SQL DBs on it as well. All the looking I've done has primarily how to deal with Veeam and SQL when you want it to back up, but I haven't been able to find much for if you don't want it to touch SQL.

# ¿ Jan 25, 2019 21:34

Adbot: ADBOT LOVES YOU

# ¿ May 16, 2024 09:01

Panthrax: Jul 12, 2001; I'm gonna hit you until candy comes out.

Potato Salad posted:

Tell Veeam not to do Application Aware Processing and it'll stop doing anything more than VSS pauses.

Without Application Aware Processing, Veeam will take crash-consistent image backups of the machine using VSS, which seems to be all you want.

I have many times brought up Veeam image-based backups of MSSQL, Oracle, and other databases lacking application aware processing into prod or validation environments without a hitch. Worst I've had to do is rerun an archive redo log after restoration when an oracle DB lost power in unfavorable conditions.

Yeah, App aware processing is already turned off, but it's still screwing around with SQL.

Here's a log from SQL side:

code:

2019-01-25 01:21:32.37 spid87      I/O is frozen on database Applications. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2019-01-25 01:21:32.37 spid92      I/O is frozen on database ArchivedCDRRECORD. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2019-01-25 01:21:32.37 spid111     I/O is frozen on database aspnet-BVOSS2api. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2019-01-25 01:21:32.37 spid112     I/O is frozen on database BulkData. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2019-01-25 01:21:32.37 spid113     I/O is frozen on database CCMI_LERG. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2019-01-25 01:21:32.37 spid114     I/O is frozen on database Costar. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2019-01-25 01:21:32.37 spid115     I/O is frozen on database CovadServices. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
<snipped for brevity>
2019-01-25 01:21:32.56 spid87      I/O was resumed on database Applications. No user action is required.
2019-01-25 01:21:32.56 spid111     I/O was resumed on database aspnet-BVOSS2api. No user action is required.
2019-01-25 01:21:32.56 spid92      I/O was resumed on database ArchivedCDRRECORD. No user action is required.
2019-01-25 01:21:32.56 spid112     I/O was resumed on database BulkData. No user action is required.
<snipped for brevity>
2019-01-25 01:21:36.48 Backup      Database backed up. Database: db_manager_CLR, creation date(time): 2016/03/19(21:08:08), pages dumped: 345, first LSN: 39:989:1, last LSN: 39:992:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{EBD1773B-7716-4568-959C-E47399BC0D26}10'}). This is an informational message only. No user action is required.
2019-01-25 01:21:36.48 Backup      Database backed up. Database: db_migrate, creation date(time): 2016/03/19(09:54:21), pages dumped: 229313, first LSN: 2053088:2521:1, last LSN: 2053088:2524:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{EBD1773B-7716-4568-959C-E47399BC0D26}11'}). This is an informational message only. No user action is required.
2019-01-25 01:21:36.48 Backup      Database backed up. Database: db01, creation date(time): 2016/03/19(23:37:54), pages dumped: 311413, first LSN: 2316773:1402:169, last LSN: 2316773:1473:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{EBD1773B-7716-4568-959C-E47399BC0D26}12'}). This is an informational message only. No user action is required.
2019-01-25 01:21:36.49 Backup      BACKUP DATABASE successfully processed 0 pages in 12.690 seconds (0.000 MB/sec).
2019-01-25 01:21:36.49 Backup      BACKUP DATABASE successfully processed 0 pages in 12.960 seconds (0.000 MB/sec).
2019-01-25 01:21:36.49 Backup      BACKUP DATABASE successfully processed 0 pages in 12.160 seconds (0.000 MB/sec).
<snipped for brevity>

Panthrax fucked around with this message at 21:18 on Jan 26, 2019

# ¿ Jan 26, 2019 21:13

Panthrax: Jul 12, 2001; I'm gonna hit you until candy comes out.

Could someone check or post their SQL logs for when Veeam takes a backup of a volume with both flat files that we want backed up (.bak, other business files) and live SQL files that we don't (.mdf and .ldf files) with app aware processing turned off? Veeam support tells me that in order for VSS to stop showing up in SQL logs, we need to remove the files we want to back up and put them on a separate drive than the SQL files, then it'll stop. However unfortunately I'm not the one talking to Veeam, and I can't tell if they're getting the whole logging into the DB thing confused with just VSS, or if we all know what we're talking about, or what.

# ¿ Jan 28, 2019 21:54

Panthrax: Jul 12, 2001; I'm gonna hit you until candy comes out.

HalloKitty posted:

Right, I actually did remember to reply with something useful.

Assuming we are talking about VBR backups (again, it might work with an agent, but I haven't tried), you need to create a registry key on the server to be backed up:

HKEY_LOCAL_MACHINE\SOFTWARE\Veeam\Veeam Backup and Replication
ExcludeSQLInstances

As a Multi-String Value

Then simply write the exact name of every SQL instance you want to exclude, each on a newline inside the multi-string value. Hope this helps.

Thanks, I'll shoot this over to the guy handling the backups to test. For this we're specifically concerned about agent jobs on physical machines, so we'll definitely need to test it. Thanks for the info!

# ¿ Jan 28, 2019 21:57

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > Backup Thread 2: Test the restore procedure!