| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2013-05-17 : 10:30:03
|
| We have a Dev Server with several Dev Databases. If we are doing Disk backups only, is that sufficient? Or are SQL Server Database backups also required. I was worried that disk backups are insufficient since Data may be in memory - or is there any other reason. What if we issue a checkpoint and then do Disk Backups (i.e. no SQL Server Backups)? |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-17 : 11:37:30
|
| Normally you would take a SQL Server backup to disk - and then take a disk backup . All transactions commited during and up to the end of the backup will be included in the backup. Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-17 : 12:41:19
|
| Absolutely take SQL Server backups as well as disk images. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-05-17 : 13:06:07
|
| It depends on what you mean by disk backups - what utility that really is - and whether or not it is integrated with SQL Server. If it is not - then most likely the backups of the database files will not be usable to recover those databases. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-05-17 : 14:34:26
|
| I am not disagreeing with anyone, but I just want to understand why.(these are backups that are not integrated with SQL Server)For recovery, could I not take the mdf/ldf from the file backups and attach them? If the issue is because there is unsaved data (i.e. dirty pages I think) could not a checkpoint solve this? Or is the problem that the mdf file could be in the middle of something so there would be no guarantee that it is up to date. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-17 : 14:38:03
|
| You need to detach to be able to attach again. btw , could you give details about the disk backup - do you mean something like FlashCopy manager - that does a block level full drive backup or do you mean standard flat file backup?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-05-17 : 15:09:32
|
quote:
Originally posted by jackv
You need to detach to be able to attach again. btw , could you give details about the disk backup - do you mean something like FlashCopy manager - that does a block level full drive backup or do you mean standard flat file backup?
I'm not too sure but I understood it is a differential backup. But maybe with mdf files, it's a single file and will always be different so maybe the differential backup doesn't mean much. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-17 : 15:53:54
|
| Consider disk backup for rebuilding OS and VMs. SQL backup for restoring SQL Server databases.Also, the most common reasons to restore are not disk failure. If you don't have SQL Server backups, you're screwed if you accidentally drop a table, or delete data. Particularly if the data was created/modified AFTER the last disk snapshot.Disk snapshots/backups are NOT a replacement for SQL backups. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-05-21 : 13:39:03
|
quote:
Originally posted by denis_the_thief
I am not disagreeing with anyone, but I just want to understand why.(these are backups that are not integrated with SQL Server)For recovery, could I not take the mdf/ldf from the file backups and attach them? If the issue is because there is unsaved data (i.e. dirty pages I think) could not a checkpoint solve this? Or is the problem that the mdf file could be in the middle of something so there would be no guarantee that it is up to date.
With an integrated utility - that utility will freeze the I/O in SQL Server, perform its action - and then unfreeze the IO. This insures that the mdf/ldf files can be attached. Without that integration there is no assurance that the mdf/ldf files will be in a state where they can be attached.These types of utilities can be used to replace existing native backups - but you really need to know the implications of those utilities and how they interact with SQL Server. For example, NetApp Snap Manager allows for a very quick SAN snapshot form a backups and is integrated so SQL Server is updated with the backup information. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-05-23 : 09:42:20
|
quote:
Originally posted by jackv
You need to detach to be able to attach again.
Thanks everyone.What about SQL Server is stopped. In that case could we backup the mdf files and attach them later? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-23 : 10:01:44
|
| Why don't you want to take SQL Server backups? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-23 : 10:03:15
|
quote:
Originally posted by jeffw8713
NetApp Snap Manager allows for a very quick SAN snapshot form a backups and is integrated so SQL Server is updated with the backup information.
And very clunky to restore if there are multiple databases on the volume.  |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-05-23 : 11:49:00
|
quote:
Originally posted by russell
Why don't you want to take SQL Server backups?
Good question. One issue is that we have a nightly backup and it is approaching 8 hours and taking longer as our data grows. And we don't want these full backups running during the "day" as that effects performance. There is an idea that we are outsourcing our servers and I heard something that they are planning to do only disk backups. So, just in case, I want to be prepared to know what works and what doesn't work and why. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-05-23 : 15:22:13
|
quote:
Originally posted by russell
quote:
Originally posted by jeffw8713
NetApp Snap Manager allows for a very quick SAN snapshot form a backups and is integrated so SQL Server is updated with the backup information.
And very clunky to restore if there are multiple databases on the volume.
Oh yeah, absolutely - which is just one of the reasons we discontinued using Snap Manager for SQL Server backups. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-05-23 : 15:26:47
|
quote:
Originally posted by denis_the_thief
quote:
Originally posted by russell
Why don't you want to take SQL Server backups?
Good question. One issue is that we have a nightly backup and it is approaching 8 hours and taking longer as our data grows. And we don't want these full backups running during the "day" as that effects performance. There is an idea that we are outsourcing our servers and I heard something that they are planning to do only disk backups. So, just in case, I want to be prepared to know what works and what doesn't work and why.
If your backups are taking 8 hours, I would recommend that you start investigating the SAN and IO subsystem setup. I have a 3TB database that is backed up nightly - and that takes just a bit over 2 hours to complete. It could be much better, but it is still well within our maintenance window so we are not too concerned yet.I do know that others have their backups completing in less than an hour - for even larger databases.The other option would be to investigate SAN based snapshots. They could reduce your backup times to seconds...but there are a lot of considerations when implementing that type of solution. You would need to investigate and research the solutions so you have a full grasp of all of the consequences of using this type of solution. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-05-23 : 15:49:08
|
you said these were dev databases?Why would you care about the data on those databases -- they aren't productionPresumably you would be able to regenerate the data from production or build some sort of representative dataset for development.Do you source control your database logic at least?Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-23 : 17:12:40
|
quote:
Originally posted by jeffw8713
quote:
Originally posted by russell
quote:
Originally posted by jeffw8713
NetApp Snap Manager allows for a very quick SAN snapshot form a backups and is integrated so SQL Server is updated with the backup information.
And very clunky to restore if there are multiple databases on the volume.
Oh yeah, absolutely - which is just one of the reasons we discontinued using Snap Manager for SQL Server backups.
If we weren't heavily using FlexClone and SnapMirror I would discontinue them as well. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-05-24 : 10:02:36
|
quote:
Originally posted by Transact Charlie
you said these were dev databases?Why would you care about the data on those databases -- they aren't productionPresumably you would be able to regenerate the data from production or build some sort of representative dataset for development.Do you source control your database logic at least?Transact Charlie
Dev and QA Databases, so that is part of it, that our requirements are not as high as for Production. The thing is there could be test cases in the data and also there are a lot of configuration settings.We are using source control but only source safe, it's good for looking at historical information regarding stored procedures and preventing overwrites. But we would not easily be able to use it for restore purposes or to apply a version. And we aren't using source safe for table changes or configuration settings. Do you believe we should be using a different tool - which one? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-24 : 11:15:36
|
| I think you need to seriously rework your backup strategy. If faster/better hardware isn't a possibility, you can:- Consider the disk layout of your databases.- Look at doing periodic differential backups- Run the backups at different times throughout the day, rather than serially all at once.SQL Server backups are your best protection against hardware failures, and accidental data modification/deletion. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-25 : 11:29:05
|
| Your first step is to define a review of the service level of agreements you have with the database owners. Once you've defined the acceptable Recovery Points and Recovery Times - build a backup strategyJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-05-27 : 08:46:57
|
quote:
Originally posted by denis_the_thief
quote:
Originally posted by Transact Charlie
you said these were dev databases?Why would you care about the data on those databases -- they aren't productionPresumably you would be able to regenerate the data from production or build some sort of representative dataset for development.Do you source control your database logic at least?Transact Charlie
Dev and QA Databases, so that is part of it, that our requirements are not as high as for Production. The thing is there could be test cases in the data and also there are a lot of configuration settings.We are using source control but only source safe, it's good for looking at historical information regarding stored procedures and preventing overwrites. But we would not easily be able to use it for restore purposes or to apply a version. And we aren't using source safe for table changes or configuration settings. Do you believe we should be using a different tool - which one?
I can recommend Redgate SQL Compare and SQL Source Control -- used at my place and they are very, very nice (SQL Compare especially) -- You can compare a database directly to a source controlled version...Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Next Page
|
Can we do disk backup only