SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Can we do disk backup only
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/17/2013 :  10:30:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2043 Posts

Posted - 05/17/2013 :  11:37:30  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/17/2013 :  12:41:19  Show Profile  Visit russell's Homepage  Reply with Quote
Absolutely take SQL Server backups as well as disk images.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 05/17/2013 :  13:06:07  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/17/2013 :  14:34:26  Show Profile  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2043 Posts

Posted - 05/17/2013 :  14:38:03  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/17/2013 :  15:09:32  Show Profile  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/17/2013 :  15:53:54  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 05/17/2013 15:54:08
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 05/21/2013 :  13:39:03  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/23/2013 :  09:42:20  Show Profile  Reply with Quote
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?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  10:01:44  Show Profile  Visit russell's Homepage  Reply with Quote
Why don't you want to take SQL Server backups?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  10:03:15  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 05/23/2013 10:03:45
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/23/2013 :  11:49:00  Show Profile  Reply with Quote
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.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 05/23/2013 :  15:22:13  Show Profile  Reply with Quote
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.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 05/23/2013 :  15:26:47  Show Profile  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/23/2013 :  15:49:08  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
you said these were dev databases?

Why would you care about the data on those databases -- they aren't production

Presumably 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
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  17:12:40  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 05/24/2013 :  10:02:36  Show Profile  Reply with Quote
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 production

Presumably 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?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/24/2013 :  11:15:36  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2043 Posts

Posted - 05/25/2013 :  11:29:05  Show Profile  Visit jackv's Homepage  Reply with Quote
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 strategy

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/27/2013 :  08:46:57  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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 production

Presumably 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 Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000