SQL Server Backup Design

EddieJennings

Related threads:
https://mangolassi.it/topic/15452/food-for-thought-fixing-an-over-engineered-environment
https://mangolassi.it/topic/15589/storage-for-on-site-backups
https://mangolassi.it/topic/14793/microsoft-sql-backup-solution

As I continue through the adventure of the first two threads, I'm turning my focus on acceptable methods for taking backups of SQL Server.

Here is what's been done since the beginning of time and has been on autopilot.

Configured within SQL Server Management Studio: Hourly transaction logs saved to local disk.
Configured within SQL Server Management Studio: Daily full backup saved to local disk.
#1 and #2 are backed up to Yosemite Backup Server

The Windows server itself isn't backed up nor (to the best that I can see) are any configurations for SQL Server.

Here is what restoration would look like. Keep in mind nothing is virtualized.
Data Restoration

Restore the full database backup.
Restore transaction logs from the point of the full database backup up to as close to the present as possible.

Server Restoration

Re-install Windows server, configure, and patch.
Re-install SQL Server, configure, and patch.
Restore the full database backup.
Restore the transaction logs from the point of the full database backup up to as close to the present as possible.

Soon(tm), this SQL server will be virtualized, and according to this article, Veeam (which I'll likely be using for backups now) handles SQL Server backups with ease. Would there still be a need to have SQL Server itself produce the nightly full database backup file?

My confusion comes from this article. Since if I would just be using SQL Server's restoration tools, I'd have to have database backup file + X transaction logs. For Veeam's restoration it would simply use Veeam Explorer to restore to Y point in time. I assume Veeam takes its last image backup and applies SQL Server generated transaction logs until Y point in time is restored. Methinks I have a misunderstanding somewhere about how Veeam works; thus, I appeal to the collective wisdom of ML to help keep me on the right path.

JaredBusch

@eddiejennings I use Veeam and still use the built in SQL backup tools and save the backup to another device because that data is absolutely fucking critical.

Worst case scenario Veeam is hosed and you cannot restore the VM properly. You still have SQL backup file.

Everything else can be rebuilt. It may take more time, but with that .bak file I can bring the business back to life.

EddieJennings

@jaredbusch said in SQL Server Backup Design:

@eddiejennings I use Veeam and still use the built in SQL backup tools and save the backup to another device because that data is absolutely fucking critical.

Worst case scenario Veeam is hosed and you cannot restore the VM properly. You still have SQL backup file.

Everything else can be rebuilt. It may take more time, but with that .bak file I can bring the business back to life.

That makes perfect sense. Especially considering "what happens if Veeam gets hosed?" As far as the backup tools, do you save that data to SQL Server's local storage, and then have some kind of task run that makes a copy of it to another device?

EddieJennings

@eddiejennings said in SQL Server Backup Design:

@jaredbusch said in SQL Server Backup Design:

@eddiejennings I use Veeam and still use the built in SQL backup tools and save the backup to another device because that data is absolutely fucking critical.

Worst case scenario Veeam is hosed and you cannot restore the VM properly. You still have SQL backup file.

Everything else can be rebuilt. It may take more time, but with that .bak file I can bring the business back to life.

That makes perfect sense. Especially considering "what happens if Veeam gets hosed?" ~~As far as the backup tools, do you save that data to SQL Server's local storage, and then have some kind of task run that makes a copy of it to another device?~~

Bah. I know the answer to that. It would be dumb to just have those files only in one place, so of course you'd have it local. Ok, time to go home and take a nap. My brain's being dumb.

Dashrender

@eddiejennings said in SQL Server Backup Design:

@eddiejennings said in SQL Server Backup Design:

@jaredbusch said in SQL Server Backup Design:

@eddiejennings I use Veeam and still use the built in SQL backup tools and save the backup to another device because that data is absolutely fucking critical.

Worst case scenario Veeam is hosed and you cannot restore the VM properly. You still have SQL backup file.

Everything else can be rebuilt. It may take more time, but with that .bak file I can bring the business back to life.

That makes perfect sense. Especially considering "what happens if Veeam gets hosed?" ~~As far as the backup tools, do you save that data to SQL Server's local storage, and then have some kind of task run that makes a copy of it to another device?~~

Bah. I know the answer to that. It would be dumb to just have those files only in one place, so of course you'd have it local. Ok, time to go home and take a nap. My brain's being dumb.

I wouldn't say it is dumb at all. You should already have a copy inside Veeam, so that's one, plus the live data, plus the place where you put the copy. Who knows, you might not have enough open storage on the local disk to make a copy there.. just a though.

Obsolesce

I'm with Jared on this one...

Backing up through the application layer (SQL) as often as you can depending on change frequency. For example, every 15 minutes.

Hourly and daily through the SQL backup tools.

At least daily full VM backup at the hypervisor level.

I prefer backups that use a standard file format, so you don't have to rely on Veeam for example to restore it. Veeam has screwed up too many times for me to want to rely on it like that.

popester

DILLY DILLY!!! .bak
I also use the maintenance plans, super easy.

JaredBusch

@eddiejennings said in SQL Server Backup Design:

@jaredbusch said in SQL Server Backup Design:

@eddiejennings I use Veeam and still use the built in SQL backup tools and save the backup to another device because that data is absolutely fucking critical.

Worst case scenario Veeam is hosed and you cannot restore the VM properly. You still have SQL backup file.

Everything else can be rebuilt. It may take more time, but with that .bak file I can bring the business back to life.

That makes perfect sense. Especially considering "what happens if Veeam gets hosed?" As far as the backup tools, do you save that data to SQL Server's local storage, and then have some kind of task run that makes a copy of it to another device?

Basically yes.

JaredBusch

@tim_g said in SQL Server Backup Design:

I'm with Jared on this one...

Backing up through the application layer (SQL) as often as you can depending on change frequency. For example, every 15 minutes.

Hourly and daily through the SQL backup tools.

At least daily full VM backup at the hypervisor level.

I prefer backups that use a standard file format, so you don't have to rely on Veeam for example to restore it. Veeam has screwed up too many times for me to want to rely on it like that.

I have never had been fail

scottalanmiller

@jaredbusch said in SQL Server Backup Design:

@tim_g said in SQL Server Backup Design:

I'm with Jared on this one...

Backing up through the application layer (SQL) as often as you can depending on change frequency. For example, every 15 minutes.

Hourly and daily through the SQL backup tools.

At least daily full VM backup at the hypervisor level.

I prefer backups that use a standard file format, so you don't have to rely on Veeam for example to restore it. Veeam has screwed up too many times for me to want to rely on it like that.

I have never had been fail

Except for statements like this.

JaredBusch

@eddiejennings said in SQL Server Backup Design:

@eddiejennings said in SQL Server Backup Design:

@jaredbusch said in SQL Server Backup Design:

@eddiejennings I use Veeam and still use the built in SQL backup tools and save the backup to another device because that data is absolutely fucking critical.

Worst case scenario Veeam is hosed and you cannot restore the VM properly. You still have SQL backup file.

Everything else can be rebuilt. It may take more time, but with that .bak file I can bring the business back to life.

That makes perfect sense. Especially considering "what happens if Veeam gets hosed?" ~~As far as the backup tools, do you save that data to SQL Server's local storage, and then have some kind of task run that makes a copy of it to another device?~~

Bah. I know the answer to that. It would be dumb to just have those files only in one place, so of course you'd have it local. Ok, time to go home and take a nap. My brain's being dumb.

I use something like crash plan pro on another computer and a hard link to the SQL back up file directory to back it up offsite

JaredBusch

@scottalanmiller said in SQL Server Backup Design:

@jaredbusch said in SQL Server Backup Design:

@tim_g said in SQL Server Backup Design:

I'm with Jared on this one...

Backing up through the application layer (SQL) as often as you can depending on change frequency. For example, every 15 minutes.

Hourly and daily through the SQL backup tools.

At least daily full VM backup at the hypervisor level.

I prefer backups that use a standard file format, so you don't have to rely on Veeam for example to restore it. Veeam has screwed up too many times for me to want to rely on it like that.

I have never had been fail

Except for statements like this.

For some reason Siri does not like Veeam yet. Even though correct it a lot