SQL TLog restore slow / handling

Availability for the Always-On Enterprise

SQL TLog restore slow / handling

Veeam Logoby VSS » Fri Jul 15, 2016 11:10 am

Hi Community,

we do a restore test from our environment including SQL TLog restore and we a scared about the speed and handling from Veeam.

- ENVIRONMENT
- Veeam v9 including all Patches
- SQL 2012 or above
- SQL Instance / DB is working --> no full restore of DB needed
- we would jump a few hours back with TLog restore

Restore like this https://www.veeam.com/blog/how-to-restore-a-sql-server-database.html but we disabled the "Perform restore to the spcific transaction" checkbox.

- VEEAM HANDLING
- mount .mdf + logs to the sql server
- delete the production .mdf without set the DB in offline mode
- creating a new .mdf from the mounted .mdf + log
- our DB is 600GB --> creating new .mdf approx. 1-2 days


Is there any option to restore to the point of Transaction (Log only restore) without recreating the whole DB, because the DB is ok and MUST NOT restored.


Thanks for help.


Greetings
VSS
VSS
Service Provider
 
Posts: 12
Liked: 10 times
Joined: Tue Nov 26, 2013 9:00 am
Full Name: Steffen Briemle

Re: SQL TLog restore slow / handling

Veeam Logoby foggy » Fri Jul 15, 2016 3:54 pm

Steffen, transaction logs can be applied to the backed up database state only, they cannot be applied to a more recent database state. If you describe your setup in more detail, we could check whether it can be optimized for faster restores.
foggy
Veeam Software
 
Posts: 14765
Liked: 1083 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: SQL TLog restore slow / handling

Veeam Logoby VSS » Fri Aug 05, 2016 6:52 am

Good Morning,

at the moment we try something with SQL Log Shipping Server...
But i think you explained it why it is so slow or so time intensive: they cannot be applied to a more recent database state --> So Veeam must first restore the older DB to restore the TLogs!

If this were to circumvent when you backup only FOREVER INCREMENTAL?


Have a nice week and greetings.

VSS
VSS
Service Provider
 
Posts: 12
Liked: 10 times
Joined: Tue Nov 26, 2013 9:00 am
Full Name: Steffen Briemle

Re: SQL TLog restore slow / handling

Veeam Logoby foggy » Fri Aug 05, 2016 11:53 am

This doesn't depend on the backup method.
foggy
Veeam Software
 
Posts: 14765
Liked: 1083 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: SQL TLog restore slow / handling

Veeam Logoby VSS » Sat Aug 06, 2016 7:15 am

This is a pity... :(
Testing continous.
VSS
Service Provider
 
Posts: 12
Liked: 10 times
Joined: Tue Nov 26, 2013 9:00 am
Full Name: Steffen Briemle

Re: SQL TLog restore slow / handling

Veeam Logoby DaveWatkins » Sat Aug 06, 2016 9:21 pm

Surely is the resotre is only going back a couple of hours, the last full DB backup is in the same state as the existing production DB (assuming a DB backup hasn't occured in the last few hours). Full DB backups only happen with a VM backup don't they, so it's very likely the existing MDF file is the same.

That would mean if you could stop the DB without committing the existing TLOG then you should be able to easily apply on the restored TLOG up to the transaction requested right?
DaveWatkins
Expert
 
Posts: 252
Liked: 61 times
Joined: Sun Dec 13, 2015 11:33 pm

Re: SQL TLog restore slow / handling

Veeam Logoby VSS » Mon Aug 08, 2016 6:58 am

Good morning,

yes, i think so but sometime you should trancate... maybe i can test these Scenario.
VSS
Service Provider
 
Posts: 12
Liked: 10 times
Joined: Tue Nov 26, 2013 9:00 am
Full Name: Steffen Briemle

Re: SQL TLog restore slow / handling

Veeam Logoby yunales » Mon Aug 08, 2016 11:38 am 1 person likes this post

also please beware when restoring SQL using the explorer it's very slow. Refer to this thread:
https://forums.veeam.com/vmware-vsphere-f24/sql-restore-speed-t33723.html#p204440

If using the export to another folder it's much faster! Seems to be a bug.

regards
yunales
Influencer
 
Posts: 14
Liked: 4 times
Joined: Wed Aug 20, 2014 7:34 pm
Full Name: P K

Re: SQL TLog restore slow / handling

Veeam Logoby VSS » Tue Aug 09, 2016 8:50 am

Hi,

thanks for this good tip. Little Problem in our environment, our produtciv DB is 600GB big and Veeam is running on SQL Express 2012 (only 10GB supported) so we should first upgrade Express to Standard for our tests.
VSS
Service Provider
 
Posts: 12
Liked: 10 times
Joined: Tue Nov 26, 2013 9:00 am
Full Name: Steffen Briemle

Re: SQL TLog restore slow / handling

Veeam Logoby yunales » Thu Sep 08, 2016 2:59 pm

Your welcome. Also the tests with Veeam B&R9 U2 have better performance. It's still not as fast as the export but definitely some improvement was made.
yunales
Influencer
 
Posts: 14
Liked: 4 times
Joined: Wed Aug 20, 2014 7:34 pm
Full Name: P K

Re: SQL TLog restore slow / handling

Veeam Logoby VSS » Fri Sep 09, 2016 6:47 am

Thanks for the tip, i will update Veeam and testing! :D
VSS
Service Provider
 
Posts: 12
Liked: 10 times
Joined: Tue Nov 26, 2013 9:00 am
Full Name: Steffen Briemle


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], michaele, mkretzer and 51 guests