Only backup MSSQL

[Anthony853]

HI forum,

I am new in Veeam backup, i have install the Veeam B&P 8.0 for testing.

In our company requirement, the OS backup and data backup should be separate.

we are running Hyper-V 2012R2.
VM OS structure: C: 50G for OS, D: 10G for System Log, M :100G for DB file(mdf), L: 20G for DB Log(Ldf)

For the OS (only C) backup and restore, Veeam working fine and good for us.

But for MSSQL server (only M and L), i can back it up but restore will fail.
while restore it will show the error
Database 'newDB' restore failed. ExlorerManagementService: Failed to get VM mount result (sessionid =XXX-XX' SQL metadata for database newDB not found in restore point XXXX-XXXX)

i have another test when backup both all disk (C,D,M and L), everything fine.

i am confuse that the DB backup why still need the OS drive.

Since in our policy, DB backup should be daily full and 15min log incl backup.

if everyday doing a full system backup. i think it is very waste disk space and time.

PS: i search in google and found this http://www.veeam.com/kb1991, already install the b2 udpate, but problem still there.

please give advice, am i missing something or Veeam backup do not support only MS SQL backup ???

[PTide]

Hi,

MSSQL database consists not only of the database files, but also it includes a bunch of supplements such as metadata files, libraries etc. All those items are usually spread across the system. Please see this guide.

There are two options:

1. You perform a backup by SQL-native means, after that you can place those backups to some separate drive and make a backup of that drive with Veeam. Such an approach would require you to use pre-freeze and post-thaw scripts in order to leverage SQL backup mechanisms proir to starting the backup job. Veeam SQL explorer will not work in this case.

2. You have to locate all the files that SQL needs to run and include in your backup all drives where those files reside at. Usually you need to backup system drive and the drive where your database file is kept. With this approach Veeam SQL explorer will work. Since your system drive does not change much during the day, after first run all subsequent backups will go faster and will include changed data only (i.e. SQL data) if you use an incremental backup model.

With all said, I think that the latter approach is a way to go. Let me know if any help/clarification needed.

Thank you.

[Vitaliy S.]

But for MSSQL server (only M and L), i can back it up but restore will fail.
while restore it will show the error
Database 'newDB' restore failed. ExlorerManagementService: Failed to get VM mount result (sessionid =XXX-XX' SQL metadata for database newDB not found in restore point XXXX-XXXX)

Do you need to restore database only? Have you tried using FLR wizard instead?

Since in our policy, DB backup should be daily full and 15min log incl backup.

Are you using SQL logs backup job for this?

if everyday doing a full system backup. i think it is very waste disk space and time.

System state doesn't change very often, so it shouldn't consume much space if you do an image backup of the VM. Only changed data will be transferred in this case.

[Anthony853]

thanks for both reply.

1. You perform a backup by SQL-native means, after that you can place those backups to some separate drive and make a backup of that drive with Veeam. Such an approach would require you to use pre-freeze and post-thaw scripts in order to leverage SQL backup mechanisms proir to starting the backup job. Veeam SQL explorer will not work in this case.

2. You have to locate all the files that SQL needs to run and include in your backup all drives where those files reside at. Usually you need to backup system drive and the drive where your database file is kept. With this approach Veeam SQL explorer will work. Since your system drive does not change much during the day, after first run all subsequent backups will go faster and will include changed data only (i.e. SQL data) if you use an incremental backup model.

so do you mean i also need backup the SQL application? if i also install the SQL application to M, will it work?

But for MSSQL server (only M and L), i can back it up but restore will fail.
while restore it will show the error
Database 'newDB' restore failed. ExlorerManagementService: Failed to get VM mount result (sessionid =XXX-XX' SQL metadata for database newDB not found in restore point XXXX-XXXX)
Do you need to restore database only? Have you tried using FLR wizard instead?

Yes, for DB backup, i only database restore only, do you mean the Veeam explorer for SQL? it fail, no matter i export it or restore it to any instance.

Are you using SQL logs backup job for this?

yes, use application-aware and set the log backup every 15mins, everything fine if i backup the full system, but we only need Database, System backup should separate to other jobs.

System state doesn't change very often, so it shouldn't consume much space if you do an image backup of the VM. Only changed data will be transferred in this case.

if only incl backup, it is fine. but as our policy, we need run Database Full backup everyday and keep 30 days, as i test, the normal C driver has 16G usage and every full system backup will spend 8G....
so if the backup include system drive, i need extra 30 * 8 =240 G space for 1 SQL backup. it's crazy for that.

[Anthony853]

HI,

i have create a new VM, install the SQL application at M, data file save at M, log file save at L.
only backup M and L, backup success, restore still fail. even export the db file also fail.

Does Veeam B&P can meet our requirement? From our company policy, we must separate the DATA and OS backup to 2 different way.
it really impossible for doing full system back up everyday...

[Vitaliy S.]

Does Veeam B&P can meet our requirement? From our company policy, we must separate the DATA and OS backup to 2 different way.

You can use Veeam to meet these requirements as well. In order to do that, you can setup two different backup jobs (what you've already done) and backup OS and SQL data with their own schedule. SQL Backup job should not be used in this case, cause it requires system OS state in the backup chain as well.

If you want to do restores, then I would recommend using either FLR wizard to recover your SQL Server databases.

[Anthony853]

You can use Veeam to meet these requirements as well. In order to do that, you can setup two different backup jobs (what you've already done) and backup OS and SQL data with their own schedule. SQL Backup job should not be used in this case, cause it requires system OS state in the backup chain as well.

If you want to do restores, then I would recommend using either FLR wizard to recover your SQL Server databases.

thanks for reply,

can i know FLR wizard can also include the log restoration?

Since some of our data will be very sensitive, point in time log data also need guarantee to restore.

[Vitaliy S.]

FLR wizard will restore any file located in the backed up volume. If you schedule to do local backups via SQL Management Studio and then backup these files with Veeam backup job on daily basis, this should do the trick.

[Anthony853]

FLR wizard will restore any file located in the backed up volume. If you schedule to do local backups via SQL Management Studio and then backup these files with Veeam backup job on daily basis, this should do the trick.

Thanks Vitaliy,

Ok, i got you meaning. i will try to test it. thanks.