June 16, 2015 at 6:59 am
Hi,
We have got a mixed environment where we have production , dev and test servers.
What we have been asked now, is why SQL Full database backup is requires when SAN backup is being taken.
My question is what is the problem if we are taking full backup.
Where are we wrong?
Is it not advisable to take backup from SQL when a third party tool is also involved?
Thanks,
Harsha
June 16, 2015 at 7:53 am
I suspect your SAN backup is only taking incremental changes.
1. The backups might work especially if you can see a device_type of 7 in msdb.dbo.backupmediafamily.
2. How often are the SAN backups being taken?
3. Are you in a position to test the SAN backup? I suspect not.
Part of your job as a DBA is to be able to RESTORE databases - a BACKUP is only part of the process.
You should be taking regular SQL Server FULL backups and checking that they can be restored. A corrupted backup is no use.
On production servers, you should also be taking regular LOG BACKUPS. For a LOG BACKUP to work at least one FULL backup is required on the DB.
https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
The SAN backup may, or may not, be fine for getting a DB is a consistant state if a server needs to be rebuilt.
Even if the DB is in a consistant state after a rebuild it will probably not be up to date. You will then need to RESTORE from your most recent SQL backups in order to minimize data loss.
To answer your question, you should be taking FULL backups, LOG backups and possibly DIFFERENTIAL backups.
June 16, 2015 at 8:15 am
See this thread:
http://www.sqlservercentral.com/Forums/Topic1694442-2893-1.aspx
What likely will happen in the event you need to restore from a SAN backup is inconstancy.
As was suggested, test your RSTORES regularly. I would suggest using SQL backups.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 16, 2015 at 9:17 am
Hi Ken,
Thank you for the reply.
The san people are telling that they are not able to take full backup as its clashing with our full backup. Due to this, their differential backup is changing into full backup.
For this reason, they are telling to take 1 day full SQL backup and then the rest of the day the storage guys will be taking the backup.
June 16, 2015 at 9:18 am
You should also find out if the SAN backups are being taken of the database files themselves - the MDFs and LDFs. If this is the case, you may not be able to use them to restore anything.
The database backup process takes things like transactions into account and also marks log space available for reuse. These things are likely not handled by the SAN backup, so you could get an inconsistent state copied out and the log files will continue to grow.
Add me to the list of people who recommends using SQL Server backups. I also think you should test your backups regularly by doing randomly-selected test restores from your backups. Without verification, you'll never know that you can restore successfully. Remember, the goal of backups is not to take backups, but to be able to restore from them.
June 17, 2015 at 5:08 am
It sounds as though the SAN backup knows nothing about SQL Server so will not be able to backup a database in a consistant state.
I suspect you will probably need to speak to your SAN people and agree some disk space which will not be involved in the SAN backup - you can then use this space for SQL backups. The SQL backups will, of course, need to be backed to tape.
This is not an area where I have a great deal of experience as, where possible, I try and keep SQL databases off the SAN.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply