AUTORECOVERY alternative for SQL Server

Is it possible to mimic Oracle SET AUTORECOVERY ON in SQL Server 2008 for applying transaction logs?

RESTORE LOG [DB_NAME] FROM DISK requires backup name; in case I have couple hundreds logs I must ensure logs are restored in proper order . Surely, I can sort files by name, but I wonder maybe there is out of the box solution.

Thank you.

Is it possible to mimic Oracle SET AUTORECOVERY ON in SQLServer 2008 for applying transaction logs?

No. SQL Server does not have this option.

What you can do ?

• Take often Full/differential backups (as suggested by @Aaron Bertrand). This highlights the importance of having a good backup (and restore) plan which is inturn dictated by your company's RTO and RPO.

• Script out and often test your restores. A backup is ONLY good if you are able to restore it. You can use sp_RestoreGene - T-sql version from Paul Brewer. It automates your restores.

• Implement Logshipping to a warm standby server. Logshipping is an automated process of backing up your primary database (Full backup followed by T-Logs set as per frequency (minimum is 1 mins)), copying the log files to a shared location on secondary and applying them on the secondary database. This will be a close option to SET AUTORECOVERY ON that you have in Oracle.

My goal is to deliver logs to standby, but apply them once a day.

This is a perfect case of implementing Logshipping. You can even delay applying transaction logs and have the secondary database in standby mode which can be used for reporting queries.

Alternatively, if you are on sql server 2012 Enterprise and up, then you can look into implementing AlwaysON Availablity groups.

StackExchange Database Administrators Q#138233, answer score: 2