September 3, 2010 at 3:19 pm
We have once a week full backups, daily differential and every two hours log backups. I have done ton of testing and figured out database can't be restored if the backups are done in the mentioned below order.
i) full backup
ii) snap shot backup ( done through SAN replication)
iii) Differential backups
iv) log backups
if i have just full , next differential and logs i am not able to restore the db.I get the error "Restore database is terminating abnormally.This differential backup cannot be restored because database has not been restored". If i just use full and all log backups i am able to restore. I am very positive that snapshot backup after the full backup is breaking but not sure why. Has anyone seen this type of behaviour before? PLease advice!!
September 3, 2010 at 3:23 pm
Please post the result of Restore Filelistonly.
September 3, 2010 at 4:45 pm
Does this work if you don't do the SAN backup?
You should be able to test this during a low period of activity. See if you can :
- Add a table
- make a full
- add a row to the new table
- make diff
- add row to the new table
- make log
test the restore. Use WITH STANDBY at each stage and check your table.
September 3, 2010 at 5:21 pm
That is normal behavior - a differential backup can only be restored after the full backup that was taken right before it. The differential backup is tied to that full backup - since it only backs up data that has changed since the last full backup.
The SAN snapshot uses VDI to record that a full backup has been taken. Once that is done, any differentials following that snapshot could only be applied after you perform a restore from that snapshot.
If your SAN snapshot utility has the capability of performing a COPY_ONLY backup, then your differential would not be affected. If that is not possible, then you need to switch the order of your backups and perform the SAN snapshot first - then perform your native full backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 3, 2010 at 8:03 pm
thanks Steve and Jeff. It makes sense now. I looked at many different microsoft articel but i couldn't find the exact syntax to take a snapshot backup. Do you know where can i find the exact syntax for a snapshot backup?
September 3, 2010 at 10:13 pm
Snapshot backups are not a part of SQL Server. They are SAN functionality. The SAN uses VDI to freeze SQL and tell SQL a backup has been performed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 4, 2010 at 1:21 pm
Ok. But the snapshot backup is just 5 mb. If you look at the order which i have mentioned in the first post based on that how can i restore. How do i restore snapshot after full backup? Also if you can point me to some articles about VDI/SAN that would be great. Thanks
September 4, 2010 at 2:59 pm
Again, a differential is based upon the previous full backup taken prior to the differential. Once you performed the snapshot backup (which is a full backup), then your differential backup is based upon that snapshot.
Here is a link about VDI: http://blogs.msdn.com/b/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx
A snapshot is not really a backup - it is a SAN feature that creates a snapshot of the volume. Any changes to the source volume are recorded in the snapshot. You need to review your vendors documentation on how they perform and manage snapshots - and how that interfaces with SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 5, 2010 at 1:04 am
You are going to have to get with your storage administrator to work and understand this, as each SAN vendor can implement snapshots in different ways. For example we use NetApp, and the standard snapshot is done at the block level versus the logical file level, which makes snapshots very fast yet small. However for SQL server, they have utilities that basically make API calls to SQL for many of the backup/restore operations. You are going to need to get the information specific to your SAN vendor and environment.
Joie Andrew
"Since 1982"
September 5, 2010 at 12:47 pm
Jeffrey has laid it out well, and Joie is correct, you need to understand how your SAN works. I have had SANS that snapshoted things without letting SQL know that a backup had taken place. However if that is not what is happening in your environment, you need to be aware so you can plan for DR.
September 5, 2010 at 8:51 pm
thanks a Ton. Steve that is what exactly happened, we didn't knew that snapshots were happening which broke our differential backups. Thanks
September 5, 2010 at 8:52 pm
Jeffrey Williams-493691 (9/4/2010)
Again, a differential is based upon the previous full backup taken prior to the differential. Once you performed the snapshot backup (which is a full backup), then your differential backup is based upon that snapshot.Here is a link about VDI: http://blogs.msdn.com/b/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx
A snapshot is not really a backup - it is a SAN feature that creates a snapshot of the volume. Any changes to the source volume are recorded in the snapshot. You need to review your vendors documentation on how they perform and manage snapshots - and how that interfaces with SQL Server.
Thanks Jeff
September 5, 2010 at 8:58 pm
Jeffrey Williams-493691 (9/4/2010)
Again, a differential is based upon the previous full backup taken prior to the differential. Once you performed the snapshot backup (which is a full backup), then your differential backup is based upon that snapshot.Here is a link about VDI: http://blogs.msdn.com/b/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx
A snapshot is not really a backup - it is a SAN feature that creates a snapshot of the volume. Any changes to the source volume are recorded in the snapshot. You need to review your vendors documentation on how they perform and manage snapshots - and how that interfaces with SQL Server.
Jeff is there any article which explains if snapshot is taken and then a differential then differential backup is dependent on the snapshot would be easy for me to explain to SAN admins:-)
September 6, 2010 at 10:48 am
As stated previously, you need to follow up with your SAN engineers. The documentation for the snapshot utility being used should document how it works with SQL Server.
If not, you need to follow up with the SAN vendor - there is nothing in SQL Server that is going to document how the SAN feature is implemented.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 6, 2010 at 4:47 pm
Jeffrey Williams-493691 (9/6/2010)
As stated previously, you need to follow up with your SAN engineers. The documentation for the snapshot utility being used should document how it works with SQL Server.If not, you need to follow up with the SAN vendor - there is nothing in SQL Server that is going to document how the SAN feature is implemented.
Thanks.Got it.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply