SQL backup

[gregb]

When I backup my MS SQL 2005 & 2008 servers with Veeam VSS integration enabled, strange full database backups are created on the servers. They are started by NT AUTHORITY\SYSTEM without any name and use VIRTUAL_DEVICE as a backup target.
These backups destroy my Full-Differential backup chain making my diff backups unusable. Has anyone come across this problem?
I want to create daily veeam backups of my SQL VMs but I also need full and diff sql backups. Is it possible to prevent Veeam VSS from creating sql backups?

Greg

[Gostev]

Greg, unfortunately it is not possible to alter the behavior of Veeam VSS (besides disabling it completely). Veeam VSS is not designed with scenario of parallel backup by other solutions in mind...

[gregb]

Greg, unfortunately it is not possible to alter the behavior of Veeam VSS (besides disabling it completely). Veeam VSS is not designed with scenario of parallel backup by other solutions in mind...

Gostev,

It looks like I'll have to disable vss.
I think there is no reason for not having a whole VM backed up by Veeam Backup and databases backed up in sql. When I need to restore a single database I don't want to restore the whole VM.

[Gostev]

I think there is no reason for not having a whole VM backed up by Veeam Backup and databases backed up in sql. When I need to restore a single database I don't want to restore the whole VM.

Makes perfect sense... but even in ideal situation (if you could alter Veeam VSS behavior to better address your current needs), the whole solution will still be far from ideal because it essentially requires you to backup your data twice. So I guess it would make better sense for us to invest in addressing double-backup requirement, instead of enhancing/perfecting the current way of doing things...

Thank you for your feedback!

[tsightler]

Since Veeam supports file level restore, you can pretty much already do database level restores with Veeam. Simply dismount the database you want to restore from MSSQL, restore the database files with Veeam FLR, and then remount the database. We've done this with great success with both MS SQL and Oracle databases.

Heck, we've even been able to restore individual tables by temporarily mounting the restored database file and then moving the table either with an export/import, or by simply using "Create as Select".

[gregb]

Since Veeam supports file level restore, you can pretty much already do database level restores with Veeam. Simply dismount the database you want to restore from MSSQL, restore the database files with Veeam FLR, and then remount the database. We've done this with great success with both MS SQL and Oracle databases.

Heck, we've even been able to restore individual tables by temporarily mounting the restored database file and then moving the table either with an export/import, or by simply using "Create as Select".

tsightler,

I tested it and it works fine I understand that when I use veeam backup in VSS mode to back up my sql servers, database files should be consistent state when I restore them using Veeam FLR.

If this is the case I can get rid of sql backups altogether.

Thanks for help.

[sqldba]

Hi,
I am unable to restore a backup from the Veeam VSS backup. I can see the file in the list and the transaction logs that goes with it. However, when I try to do a file and file group restore within MSSQL Management Studio I get the following error:

MSSMS - Restore failed for Server 'svrnm'. (Microsoft.SqlServer.Smo)
Additional Information:
An exeception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot open backup device '{ED58CC5B-E131-4B27-9CCE-282845CDD7C0}45'. Operating system error 2 (The system cannot find the file specified).
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3201)

For some reason the device that the Backup was performed on is not recognized. I am told the backup is occurring on the E drive of my Backup server.

How does VEEAM or VSS interpret this backup so I can restore it to MSSQL 2005 or 2008.

Thanks,
sqldba

[Gostev]

Danette, can you please clarify if you are unable to restore MDF/LDF file from Veeam backup file, or this worked fine - but there is some operation in Management Studio that fails? Unfortunately I do not know SQL Management Studio well, so I will probably be of little help here.

Not sure about your question on Veeam interpretation of SQL backup... our product perform backup on image level (entire VM image), we do not have application-specific agents, or any logic specific to backing up SQL server.

[sqldba]

Hi,
The VEEAM restore of the mdf, ldf files was perfect. My problem is I need the Transaction log files that come after the backup. How SQL works is we do full backups and then do transaction log files. The transaction log files provide additional transactions since the backup. Without the restore of the transaction log files that go with the VEEAM backup, I am unable to use the backup's for much more than in test or development situations or production databases that don't have transactions between backups.

While the VEEAM VSS backup provides a stable mdf and ldf files, they do not provide transactions that occur after.

Thanks, sqldba

[sqldba]

Hi,
Within MSSQL you can do a restore, chosing the backup and the transaction logs but I get the error above. What I want to know is how do I get access to the file (backup device) that is specified in MSSQL:

Cannot open backup device '{BF453193-F000-475B-92B9-1DD13CC23461}1'.

Here is the code that was scripted from SQL:

RESTORE DATABASE [AdventureWorksDW] FILE = N'AdventureWorksDW_Data' FROM TAPE = N'{BF453193-F000-475B-92B9-1DD13CC23461}1' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [AdventureWorksDW] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorksDW\AdventureWorksDW_backup_201005031505.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [AdventureWorksDW] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorksDW\AdventureWorksDW_backup_201005031510.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [AdventureWorksDW] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorksDW\AdventureWorksDW_backup_201005031515.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [AdventureWorksDW] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorksDW\AdventureWorksDW_backup_201005031520.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [AdventureWorksDW] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorksDW\AdventureWorksDW_backup_201005031525.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [AdventureWorksDW] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\AdventureWorksDW\AdventureWorksDW_backup_201005031530.trn' WITH FILE = 1, NOUNLOAD, STATS = 10
GO

[Gostev]

Hmm, I have no idea what Management Studio is trying to do here, but may be someone else from community can assist and explain.

[tsightler]

It appears that you are choosing "Restore Database from Backup" which is going to cause MSSQL to restore the most recent SQL managed full backup (which is apparently on tape) and then the logs, which are on disk.

Since you already restored you database with Veeam, you only want to apply the transaction logs, which I assume you still have on disk. The means you should select Task...Restore and then Transaction Log to get to the Restore Transaction Log screen. Then select the Database or the specific transaction log files you want to apply (there should be an options labeled From previous backup or From file or tape).

If you still need assistance please let me know the exact steps you are taking to restore the database.

[sqldba]

Hi,
Things that I have tried:
1. We did the mdf, ldf attach which worked great. But there were transactions that happened after the backup that I needed in transaction log files.
2. When I tried to do a restore of the transaction log files that option is greyed out.
3. If I choose the logs from the restore files and groups, it insists that I take the full backup which it claims is on tape. But the backup has never been put to tape it still remains on a disk, but on a remote server the BACKUP server.

At this point my question would be can I somehow change the status of the database to acccept the transaction log from step 1 above. Or can I obtain this backup from tape which I believe is the backup that exists on the E drive of the BACKUP remote server.

\\BACKUPSVR\e$\?????? (below is the script that I generated when I did the restore file and file groups within MSSMS)
RESTORE DATABASE [AdventureWorksDW] FILE = N'AdventureWorksDW_Data' FROM TAPE = N'{BF453193-F000-475B-92B9-1DD13CC23461}1' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

Basically, your solutions are elusive to me where the transaction log is greyed out and I cannot seem to obtain the full backup (which I don't need) when I do the file and filegroups option.

Thanks, sqldba

[Bunce]

Were you actually performing 'transaction log' backup's in SQL?

[sqldba]

Yes, since the mdf and ldf restore well, the issue is that I cannot restore the log files individually.

[tsightler]

Right, so I think the issue is that your MDF and LDF files are being restored by Veeam in a "consistent" state, that is, MSSQL is not aware that they need recovery. I think this is a change in behavior for the SQL VSS Writer between SQL 2000 and SQL 2005/2008. I think my testing was done with SQL 2000. With SQL 2005/2008, I believe that the VSS Writer has the option to set a flag which tells the database whether to immediately perform recovery, or to leave the database in the "needs recovery" state.

I believe that there is a way to get SQL 2005 to behave the same as SQL 2000 for VSS restores, but I don't know it off the top of my head. Most of my testing with Veeam was with SQL 2000 so this is a good exercise for me as well as we've moved the majority of our systems to SQL 2005 in the last few months and we definitely need to be able to preform full restores from Veeam and apply transaction logs.

I think you might also be able to attach MDF file without the LDF and then apply logs, but I haven't done that in a long time.

[sqldba]

Hi Do you know anything about this:
Prepare for Restore
In preparing for a restore, a requestor uses the stored Backup Components Document to determine what is to be restored and how. The requestor will select the components to be restored and sets appropriate restore options as needed.
Note If a backup application intends to apply differentials or log backups during the restore operation for this backup, such as when a “Restore with no recovery” is needed, the following option should be set as part of component creation for each database that is being restored.
IVssBackupComponents::SetAdditionalRestores(true)
After all the needed details are set in the Backup Component Document, the requestor makes the IVssBackupComponents::PreRestore call to generate a Pre-Restore event through VSS that will be handled by the writers

And if this is what I need to restore the log files - where does one set the component creation.

Thanks,

[Michael_6835]

Silly question,

Was your recovery model set to "Full" in the database properties?


If it is was set to anything other than "full" you wound not get transaction logs.

So if you were using "simple" you would get a full backup of the mdf/ldf but no translog backups.

[sqldba]

You are right it is a silly question since I have transaction .trn log files to do a restore with. The issue is that there is something missing between MSSQL and VSS/VEEAM backup. I have a .mdf and .ldf file that I attach great. But then it leaves me in a state where I am unable to then restore the .trn files that come after the VEEAM backup. I have read in the SQLWriter paper that there is a way to set recover/norecover, I am guessing with Visual Basic. But I don't know much about VB or how to run the commands:

IVssBackupComponents::SetAdditionalRestores(true)

Is anyone familiar with this command and what one uses to run to this command. Not sure if this is the solution, but right now it appears that the database is set to no do additional restores.

Thanks, Danette

[sqldba]

Hi to the VEEAM community: I got this response from someone who reviewed the following document
http://msdn.microsoft.com/en-us/library/cc966520.aspx


That would be up to the VEEAM system. Most likely it is unaware of SQL Server and cannot establish a "point in time" recovery start point. This is one major reason I distrust Storage or Virtualization based "recovery" techniques for SQL Server. Most such "solutions" do not understand how SQL recovery works and treats it as just another workload, completely disregarding SQL Server's implementation of point-in-time recovery.

The document references VSS API calls that the backup/restore application (VEEAM) must implement. I would suggest asking them how you can use a VEEAM restore as the beginning of a SQL log-based recovery.

Does anyone know anything about the VSS API calls and how to make setting for SQL point in time or log-based recovery.
Thanks,

[sqldba]

Ok, so I was posting back in May of last year and I still have the same situation. I am not able to do a full restore with transaction logs from my VSS/VEEAM backups. Has anyone finally done a restore with MSSQL 2005/2008 and been successful in using the .trn logs that go with the backup?

It would be great to finally resolve this issue, currently we have to do two backups MSSQL backup so I can use my transaction logs after a full backup and the VSS/VEEAM to backup the entire server. It would be nice to just use the VSS/VEEAM as it is slick when trying to restore database in their full state.

Thanks for any assistance.

[ccrichard]

I don't think Veeam will do transaction logs....what if you just run the Transaction log backup everyday before the Veeam backup

[sqldba]

Yeah, I know VEEAM will not do the transaction logs. I have MSSQL running backups and transactions logs. The logs are created on an hourly basis. When a server crashes or something wrong happens in a database, you would want a point in time recovery. Example: So the backup was done at 11 PM, and I have transaction logs every hour thereafter. I want to restore the database as of 7 AM lets say. So I would use the VEEAM backup from 11 PM and all transaction logs between 12 AM and 7 AM.

If I try to do a VSS/VEEAM restore using Microsoft Management Studio, I get an error that it failed to open the backup device. Does any one know a way to restore the connection that was used during the backup. Is this done through VEEAM, VSS or can I use a "net use" command? Or is there something through VEEAM that we are missing?

ERROR:
BackupTapeFile::OpenMedia: Backup device '{306C36B2-0823-4FED-8102-3934C34BDE19}5' failed to open. Operating system error 2(The system cannot find the file specified.).

[sqldba]

I believe I have the answer to my question. In version 5 (we are using 4) it appears that there is a wizard that will do the type of restore that I am looking for. I have found a reference to this in frequently asked questions of version 5, it is:
Universal AIR Wizard

Q: How this "univeral" recovery is supposed to work?
A: Once the lab is prepared, Universal AIR wizard will provide masquerade IP address of requested VM in the isolated environment, and update routing on current computer automatically to enable transparent access. You can then use native management tools to extract required items from the application, and put them back to production server. For example, you can use free Oracle SQL Developer to perform item-level recovery from Oracle database, or Microsoft SQL Management Studio to perform item-level recovery from Microsoft SQL database, or MySQL Workbench to perform item-level recovery from MySQL database, etc. - any application!

Is this only with version 5 or do you have some other wizard in version 4?

If this is only if version 5, then we will await that version.

Let me know. Thanks,

[Gostev]

Yes, this is for version 5 that was released in October last year.

[sqldba]

Thanks, this will need to be tested, but it sounds like what I am looking for.

[tsightler]

I'm curious, how is that what you're looking for? Based on your previous posts you are looking for point-in-time recovery of your SQL databases and, as far as I know, SQL AIR or U-AIR doesn't provide anything like that at all, it just allows you to extract individual items from a SQL database from the time of the backup.

[sqldba]

I do not have veem 5 and I have done 0 testing. But based on the the statement below, it sounds like you sould be able to do a MSSQL restore, because the IP address that the restore is looking for will be revealed. When I try to do a restore from a VEEAM backup now, it gives me the following errror:
System.data.sqlclient.sqlerror: Cannot open backup device

If someone has VEEAM 5 with MSSQL 2005 or 2008, and can do a restore using the VEEAM backup and transaction logs, I would love to see it. However, for us we do not have VEEAM 5. I will need to wait until a test server is created to me to test my belief that the backup and transaction log files that I currently see in MSSQL Management Studio, will be successfully restored once the connection (masquerade IP adddress) is made available to the MSSQL Server. Not sure if someone at VEEAM has another understanding of the process.

STATEMENT:
Q: How this "univeral" recovery is supposed to work?
A: Once the lab is prepared, Universal AIR wizard will provide masquerade IP address of requested VM in the isolated environment, and update routing on current computer automatically to enable transparent access. You can then use native management tools to extract required items from the application, and put them back to production server. For example, you can use free Oracle SQL Developer to perform item-level recovery from Oracle database, or Microsoft SQL Management Studio to perform item-level recovery from Microsoft SQL database, or MySQL Workbench to perform item-level recovery from MySQL database, etc. - any application!

[tsightler]

Right, you posted the message before, but it was quite obvious that the system was unable to find the transaction logs. You have to place the transaction logs somewhere where SQL Mgmt Studio can find them.

If I were you I'd forgot about Veeam completely, and perform some backups and restores of your SQL databases using SQL Mgmt Studio ignoring Veeam completely. Once you understand how this process works, then you'll know how to perform the process with Veeam. Veeam does not do anything other than backup the VM. When you restore with Veeam you restore the database as it was at the time the Veeam snapshot was taken. Veeam uses VSS to make sure the database was transactionally consistent at that time and thus can be restored. If you want to be able to restore your transaction log backups then I would suggest again that you use the native tools for your SQL backups, both full and transaction log. Then you can use those to perform a point-in-time recovery after a Veeam recovery.

[tsightler]

Just to be 100% clear. Veeam, no matter the version, does not currently support a "point-in-time" recovery scenario. If you are looking for "granular item recover", then Veeam 5 will be able to help you. However, what you have described in your previous post is "point-in-time" recovery, i.e. transaction log replay.

In a "point-in-time" recovery you make online transaction log backups significantly more often than full backups. Typically you would make a full backup once a day, or once a week, based on database activity, and then a transaction log more ofter, perhaps once an hour, or even once every few minutes, based on your RPO. If you needed to restore the database you would restore the full backup, and the restore the transaction logs up to either the very last log backup that you had, or, in the case of corruption or a bad transaction, you might select to restore only up to a specific point-in-time.

For example, if you make full backups at midnight, and then hourly transaction log backups, and someone drops a table or deletes important records at 10:15AM, but you don't discover it until noon, you would restore your full backup, and then restore the transaction logs until perhaps 10:14AM, right up until the minute prior to the error.

Veeam does NOT support this type of recovery. You might be able to use the AIR feature to recover the deleted items and restore them anyway, but they would not be to a point in time. For example, assuming you were using Veeam to backup your SQL server in the same scenario as above. Veeam would make a backup at 12 midnight. Then, someone dropped a table at 10:15AM and you need to restore it while keeping the rest of the database. Well, assuming this table had no interrelationships with other tables, you would be able to grab the table from the Veeam backup from midnight and restore it, but any changes made from midnight to 10:15AM would be lost.

If you want point-in-time recover with Veeam, you should continue to perform native full SQL backups with native transaction log backups AND you should disable Veeam's truncation of transaction logs (if you don't do this you still won't be able to restore the full backup and the transaction logs and Veeam's backups will break the transaction log chain of the native tool).

The setup of native SQL backups with SQL Management Studio is trivial, and you can still use Veeam as a supplement, allowing you to "Instant Restore" the SQL server and then pick and choose which databases might need a specific point-in-time recovery, but Veeam, in it's current state, is simply not going to replace native SQL backups if you need granular point-in-time recovery.