@Cathy: "SQL Server has always been quite good in providing its own backup facilities 
and admins have always been taught to religiously setup backups in case 
there is any data loss.
I have on two different occasions in two different companies reviewed their 
backup strategies and it would seem that there is a very easy duplication 
(if not more) of backups being made.
The thing is most dba's seem to query with their clients, how much needs to 
be backed up and how regular backups need to be. They then setup a backup 
regime, normally to hard disk, to compliment such a regime. (lets assume 30 
days, which could easily mean a full backup weekly and a differential backup 
daily plus log files backups to allow for point in time recovery - which I 
seem to have observed to almost be a defacto standard)
However nowadays, the infrastructure team have upgraded their hardware into 
a vsphere environment with all the benifits that this brings and have 
managed to implement an amazing backup regime where all virtual server are 
backed up fully on the weekend and incremental backups taken every 
weeknight.
My observations.
1. It seems to me that if I restored one instance of a SQL Server in a 
Vsphere environment, say for 30 days ago, that restored server will 
effectively have 30 days worth of SQL backups on it for 30 days previous.
2. The same could be said for any day I restore to, which should mean, I 
have 30 X 30 = 900 backups I could possibly go back to. This seems a mere 
waste of resources and overkill
3. Should my vsphere server fail (assuming complete failure) today, and I 
restore back to last nights vsphere server, chances are I probably do't have 
the log file from today to restore to a given point in time. (I guess the 
log file needs to be stored on a different physical drive which in a cloud 
of vsphere computing, I often wonder how one knows if it is really a 
different physical drive, or should I therefore put it on a different 
physical vsphere host?)
My solution
I am therefore thinking the right solution is
Set all databases to Simple and don't bother setting up any backups. It will 
boost performance
In the most unlikely event of a failure (since vsphere is almost unbreakable 
with multiplr raid arrays and the works), simply restore the vsphere to the 
previous night, and lose the data that was changed or advise the people that 
changed the data to re-enter the data. (I understand that this might not be 
a very plausable solution in say a banking arena, but re-considering my 
observations above, I really struggle to justify more than this)
I would like to see others comments on this (I know some will argue against 
having SQL running in Vsphere, but I do not wish to get involved in that 
argument, I am looking to make it work in a vsphere environment)
TIA for any comments
C"
    @Cathy: "<quote>
I think therein lies the crux of the matter. In my humble view many Admins 
seem to default to FULL backups with log file backups without considering 
what the actual requirement are.
In many cases I would argue that where systems are only utilised 
occasionally, say for example a bulliten board on a website which normally 
has one posting a week, but has the potential of having say 10 postings or 
more in one day.
Depending on the content of the Bulliten board, I would argue that even if I 
lost the database on the day that 10 postings were made due to technical 
errors, then I could easily do with losing the 10 postings, restoring to the 
previous night and issuing a sincere apology on the website for the loss of 
posts and asking anybody who posted to re-post their posts.
If on the other hand, I was taking online payments, I could see there is a 
distinct argument for taking regular log file backups. However at the same 
time, I would be worried that if there is a complete failure of the system, 
I would not have the log files stored since the last full backup to tape, 
and therefore would implement some way of the system recording all 
transactions elsewhere by means of perhaps emailing each financial 
transaction or I guess ensuring that such a transaction is recorded on a 
remote system to allow me to at least contact all users if there is a system 
failure and re-assure them that their transaction is being dealt with 
regardless of the system being temporarily unavailable, or the loss of 
transaction data once system is restored from tape.
In a banking environment, or on a website like ebay, it is much more 
important to have the ability to restore to a specific point in time.
Any thoughts or further debate on this will be appreciated.
Of course its always worth testing to ensure a restore is possible and I 
have certainly done this. In my case, since system availability is only 
required during office hours, I can afford to shutdown the database and 
entire qmiller@example.org. Then have Vsphere backup the server in a shutdown 
state and restart the server again.
Since the server is shut down normally, the log file is closed and MDF file 
safe to backup. I do actually do a normal backup although I do wonder why, 
as simply restoring the Virtuual server should be sufficient.
Out of interest, I originally did this for a Sharepoint server farm 
environment for a company intranet. One SQL Server with two Web servers and 
one application server all built in VM environment. In this instance we used 
powershell to shut down all 4 virtual servers at midnight, then do backups 
of the servers in shutdown state and restart the servers after backup.
Sharepoint does offer further backup solutions within the sharepoint, so it 
seems silly to have the shrepoint guy run his backups, the sql guy run his 
backups and the VM Admin backup, effectively creating three backups every 
night.
regards
C "
    @esquel: "Cathy (<email_address>) writes:
<quote>
The sad truth is that many DBA has a backup strategy, but no restore
strategy.
If you see your IT Department as a company within your company that
offers services to your customers, you should have SLA (Service Level
Agreements) with RPO (Recovery Point Objective) and RTO (Recovery Time
Objective). That is, in case of a failure, how much data do you accept
to lose? How much downtime can you accept?
Backups is only one piece in the puzzle. For critical systems, a restore
is not likely to fit in to the RTO, so you need HA solutions like 
mirroring or availability groups.
But since you offer a services - and charge for it - it is up to the
customer. If a department thinks their system is very important that
data loss is out of the question and RTO in case of a total failure
is one hour, they will have to pay through the nose.
Yes, if the VM is shutdown and SQL Server is shutdown cleanly, I think a
VSphere backup should be fine.
I made my point, because I'm afraid many people take backups, but they
never test their backups, and then one day there is a disaster, they find 
that something is broken - or that they simply do not understand the 
routines. 
I once helped one of our customers when their disk went belly-up. My plan
was to restore to a point in time some half an hour before the complete
failure, but I found that the backup jobs were set up so that one
piece of the log backup was dropped. Thankfully, the failure happend
on a Saturday morning, so the hours lost were not critical - but if
the failure had happened on a Wednesday 21:00, they would have been
in very big problems. (And given the accelerating corruption going
on, it was probably just as well that I could not apply the transaction
log.)
Erland Sommarskog, SQL Server MVP, <email_address>
Links for SQL Server Books Online:
SQL 2008: <url>
SQL 2005: <url>"
{'source': 'USENET', 'other_information': '{"reply_count": 2, "forum_name": "microsoft.public.sqlserver.setup.20140623.mbox"}', 'file': 'data/output/usenet-microsoft.public.parquet'}