June 6, 2014 at 8:21 am
Our backup system has worked ok for us to date. We can restore back to either full saves or up to a certain log (we take log backups on the hour). We've never had to, but wanted to test restoring to a point-in-time with the backup data.
What the system does is generates .mdf and .ldf files, which is essentially a full backup say in the middle of the night. It then creates .bak files for the log backups based on the backup set you want to restore.
I can detach the database and apply the .mdf and .ldf and re-attach the database, but to apply the .bak files I need to get the database into a (recovering) state. I can't seem to do that. Otherwsie when I try to apply the .bak files the system says: The log or differential backup cannot be restored because no files are ready to rollforward.
Any thoughts on how to apply a .mdf and then apply the .bak files?
TIA for your help,
Doug
June 6, 2014 at 8:31 am
dough-378918 (6/6/2014)
Any thoughts on how to apply a .mdf and then apply the .bak files?
You don't.
Copies of the mdf and ldf are not backups. To restore point in time, you need a database backup, you then restore that with the norecovery option, then the log backups.
To take a backup - BACKUP DATABASE <db name> TO DISK = ...
To restore, first
RESTORE DATABASE <database name> FROM DISK = <backup location> WITH NORECOVERY
Then the logs
RESTORE LOG <database name> FROM DISK = <log backup location> WITH NORECOVERY
repeat for all log backups in sequence
Finally
RESTORE DATABASE <database name> WITH RECOVERY
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2014 at 8:59 am
Ok. I've read that process on various sites.
Since the software is generating a .mdf, .ldf, and then .bak files, I figured there must be a way to use those to recover.
How do you find the virtual device info in the database?
sys.backup_devices is empty in the db.
When you use their GUI interface and capture the statements, you'll see info like:
RESTORE DATABASE [doug_info] FROM VIRTUAL_DEVICE='{26D41E15-8FF0-44D9-9ED3-B6EF266586B1}' WITH SNAPSHOT,REPLACE,BUFFERCOUNT=1,BLOCKSIZE=1024,SNAPSHOTRESTOREPHASE=1
RESTORE LOG "doug_info" FROM VIRTUAL_DEVICE='{2A54E6D7-1B33-4A62-8EA8-151BD5207C25}' WITH NORECOVERY
etc.......
RESTORE LOG "doug_info" FROM VIRTUAL_DEVICE='{83C7C7D5-1C45-4D92-BBB9-529B76BDDADB}' WITH RECOVERY
I assume the .bak files relate to the RESTORE LOG statements. Seems like I need to figure out what the RESTORE DATABASE VIRTUAL_DEVICE is referring to. I know if must be a database backup, but not sure how that relates to the files it's giving me when I tell it to create files for the recovery. Main thing is the GUI doesn't allow you to enter a point-in-time, so I trying to figure out how to use what it's giving me.
June 6, 2014 at 9:12 am
Doug,
What is the name of the software system you are using for the backups? Is it something developed in-house or from a third-party vendor?
June 6, 2014 at 9:21 am
eVault Long story why we have it. It does do what we need, except for it being a bit cumbersome to restore, and especially this point-in-time right now, unless I'm missing an easier way they have.
I may have to end up contacting them. Just was wondering if I could figure it out on my own or with some help here.
Right now, I'm assuming there's some way to do it with the files they generate.
I can do it in a "clunky" manner where I use their GUI to apply the nightly full backup and they give you an option to restore using No Recovery option. I do that. Then I do the RESTORE LOG statements, using the STOPAT to bring it up to a point-in-time.
So.........it's not like I can't do it, but I'm sure there's a more staightforward way to do it.
Thanks.
June 6, 2014 at 9:34 am
I've never used evault for backing up SQL Server databases so I can't really help further sorry, but I'm guessing if the software says it is doing hourly log backups there must be somewhere in the GUI for evault you can configure this for the restore. I'd have a nosey around the GUI for a while to see what I could find or go the online help for the application.
June 6, 2014 at 10:19 am
Fundamentally, as long as this product correctly interfaces with VSS and the VDI (which I'm sure eVault does), it is possible to restore the mdf and ldf file via the RESTORE SNAPSHOT syntax, then go on to restore logs. You should see that the VSS backup triggered copy_only backup entries into msdb.dbo.backupset.
However, AFAIK, you must do these restores through the VDI, which is an API, meaning you need to do the restores via the software and cannot just drop the files onto the file system somewhere and run restore commands manually.
June 6, 2014 at 10:47 am
Appreciate everyone's help.
I'm taking off for the day, so I'll look at this on Monday.
I'll pursue the RESTORE SNAPSHOT process. That sounds promising.
Their interface does allow you to create files instead of directly updating the
database and creates these .mdf, .ldf, and .bak files so it seems I'm supposed to
to able to use those files to restore as I need.
Thanks again for everyone's help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply