Applying MS SQL logs w/o SMSQL

kutner · ‎2010-01-19

I have a situation where the customer can not use SMSQL, but they do run SQL on NetApp. So we are looking for a way for them to take a snapshot and then be able to use flexclone to get a copy for recovery purposes. The catch is they will need to roll forward on the recovery through the logs.

So the question is.... how can they take a backup of SQL and roll through logs but without dumping the database somewhere? Basically they will have a SQL database and the logs, so what can they use to apply the logs to the database.

I know this might seem like a simple question, but I don't know a lot about this aspect of SQL.

Thanks

radek_kubka · ‎2010-01-19

Hi,

Bear in mind that SDW doesn't leverage VSS & cannot put SQL in a hot-backup mode - hence snapshot / flexclone should be really taken when the database is offline

As to replaying logs - I don't think it is easily doable without tight integration with SQL via VSS (like in a case of SMSQL). Other than that, SQL db should recovered from SQL dump & then logs can be replayed:

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx#id0060077

This is another bit I just found on Technet about it:

http://technet.microsoft.com/en-us/library/cc966520.aspx

Full restore with additional rollforwards

The requestor can issue a restore specifying theSetAdditionalRestores(true) option. This option indicates that therequestor is going to follow up with more rollforward restores (such aslog restore, differential restore etc.). This instructs SQL Server notto perform the recovery step at the end of the restore operation.

This is only possible if the writer metadata was saved during the backup and is available to the SQL writer at the time of the restore. The SQL Server service must be running before the requestor directs VSS to perform the restore activity.

Would be interesting to see if someone knows any different though...

Regards,
Radek

sourav · ‎2010-01-19

Hi,

Please let me know if this is an accurate summary of the customer requirement: -

1. Take backups of SQL Server databases using NetApp snapshots. The customer does not want to take native SQL Server backups.

2. In the event of a recovery, the procedure should allow for a database recovery from the above full backup plus additional TLOG backups should also be applied to the full backup to complete the recovery.

Regards,

Sourav Chakraborty

Technical Marketing Engineer

SMAI , NetApp

sourav@netapp.com

kutner · ‎2010-01-20

Right process. Here's the specific question they had:

If they didn't want to do a full recovery, but just roll forward 'some' of the logs, how can they do that? They are under the impression that they need to roll forward through all of the logs and don't have utility to do just some of the logs.

Thanks,

Andy

radek_kubka · ‎2010-01-20

Hi,

Here is know-how:

SQL 2008 - http://msdn.microsoft.com/en-us/library/ms177446.aspx

SQL 2005 - http://msdn.microsoft.com/en-us/library/ms177446%28SQL.90%29.aspx

Bear in mind following note:

Restore the previous backups using the following Recovery state option: Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

This basically confirms what I said earlier - you have to recover from SQL backup (dump) first & then logs can be restored. So to me it's highly unlikely FlexClone can help in this mix.

Regards,

Radek