Backup questions for 612GB database!

Question

Backup questions for 612GB database!

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 1

To add insult to injury, vCenter backups will be controlled by your server or VM experts and that takes control of the backups away from the DBAs and created a dependency on the Server or VM employees whenever a restore is needed. Could be problematic.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

akhamoshioke (5/2/2014)
...Take daily differentials (That way, you can do a restore with stop at if you need to do point-in-time recovery...

You need to use log backups, not differential backups, for point-in-time recovery.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Michael Valentine Jones (5/2/2014)

akhamoshioke (5/2/2014)
...Take daily differentials (That way, you can do a restore with stop at if you need to do point-in-time recovery.
or
Take log backups every 15 or 30 minutes with truncate (so you can release space back to the disk).
...

You need to use log backups, not differential backups, for point-in-time recovery.

Backup of transaction logs with TRUNCATE_ONLY is not an option in SQL Server 2008 and above.

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 4

Very True Mike;

The undocumented option will be to use

BACKUP LOG MyDb TO DISK=’NUL:’

Just like TRUNCATE_ONLY, this solution has a ton of drawbacks and compromises your recoverability. This should only be done in cases where a log has grown out of control and must be erased or else the system may crash. In any other situation, you should consider backing up the log with conventional means.

We can fake it by not writing our backup to a real device. SQL Server lets us use the NUL: location as a backup target, so the following will do a log backup without actually saving the contents anywhere.

Still not the best as it affects the ability to recover from the log but given the above scenario, i don't see why it can't be used.

An alternative will be to switch the recovery model to simple and then back to full which, again, will reset the lsn and require a full backup before sql server begins normal log operation in the new mode.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

akhamoshioke (5/2/2014)
Very True Mike;
The undocumented option will be to use
BACKUP LOG MyDb TO DISK=’NUL:’

Documented, just bloody stupid (the NUL device will be documented in Windows documentation since it's a OS pseudo-file)

This should only be done in cases where a log has grown out of control and must be erased or else the system may crash.

No. If the log has grown out of control and must be discarded, you switch to simple recovery. That way the log chain is broken (just as with backup to nul), a full or diff backup is needed to restart the log chain (just like with a backup to nul) and SQL knows it (unlike with a backup to nul).

With a backup to NUL, SQL doesn't know that the log chain is broken and so allows future log backups to be taken without error. They're all completely useless for restore, but because SQL doesn't know that it won't throw errors.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Andrew G (4/30/2014)
I agree with Gail, you should be backing up to a separate disk or raid group/set of spindles, disable SQL backup compression, then use Backup Exec 2012 to do data deduplication and store it on tape. I'm assuming the tape backup is the performance bottleneck.
You should first test the theoretical max backup performance by writing to the NUL device.
Adjust the BUFFERCOUNT and MAXTRANSFERSIZE (1MB) and BLOCKSIZE.
-- Try different MAXTRANSFERSIZE
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 524288
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 1048576
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 2097152
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 25, MAXTRANSFERSIZE = 4194304
--Try different BUFFERCOUNT with best MAXTRANSFERSIZE
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 10, MAXTRANSFERSIZE = 2097152
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 40, MAXTRANSFERSIZE = 2097152
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152
--Try different BLOCKSIZE
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152, BLOCKSIZE = 1024
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152, BLOCKSIZE = 16384
BACKUP DATABASE [dbname] TO DISK = 'NUL' WITH STATS = 10, BUFFERCOUNT = 100, MAXTRANSFERSIZE = 2097152, BLOCKSIZE = 65536
Once you find the max possible speed, backup to mulitple files on a raid or SSD.
Good example here:
http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks
Im backing up a 250GB database in around 12 minutes. Max backup speed was 340MB/sec with Raid5 disks using NUL device and 280MB/sec with 8 backup files.

Since "NUL" is the 3 letter synonym for "bit bucket", where do any of the backups above go to? Where are the files to be found for the table backup? Not being argumentative... I'd like to know because 12 minutes for a 250GB database is fantastic but where would you do a restore from if such backups are made?

My feeling is that no restore would be possible from such backups.

Or, are you suggesting that those are just tests to see what might happen? If so, what's the backup history going to think of those if you need to do a restore from that time period?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Jeff Moden (5/3/2014)
My feeling is that no restore would be possible from such backups.

It's not, but that's not the point.

The method he listed is how you tune backup speed. Backup has two parts, reading and writing. By backing up to NUL you eliminate the writing phase and can tune the reading phase using the buffer count and buffer size. Once you've got the read portion as fast as possible, then you start fiddling with the write portion, backing up to different numbers of files, different locations, etc

Once you've worked out what the optimal buffers and output files are, then change the normal backup job to use those settings. There isn't really any other good way of figuring out what's optimal as it depends on hardware, database size, IO throughput, storage config and more.

You don't restore from that point in time. This is something you do in a maintenance period or preferably on a test server identical to prod (yeah right), with the app offline, a solid and tested backup taken before and a solid and tested backup taken after.

The method comes from either a whitepaper or one of the MCM prep videos, can't remember which. Think it was a whitepaper.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Thanks Gail. That's a pretty cool thing then. Hat's off to whomever came up with it.

Shifting gears, we're working with a database that's only about 310GB... if nothing else is going on at the same time, the backups for that DB take about 4 hours. If Backup Exec is running at the same time, it takes 6 to 10 hours. Of course, that's our fault because we're backing up to Network Attached Storage instead of a nice raid array on a SAN somewhere.

Still, it seems like the claim of being able to backup 250GB in 12 minutes is insanely fast to me (keeping in mind that I've been wrecked by NAS backups). Is backing up to the RAID on the SAN THAT much faster? Could I be backing up my piddly 310GB database to disk in 15 minutes instead of it taking at least 4 hours?

Heh... (actually, I should be crying) and yeah... I know one test is worth a thousand expert opinions on something like that but I can't do the test because (drum roll please) I don't have the room on the SAN to be able to do the test.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

BTW and I'm sure that someone else already thought about it and just hasn't posted it, yet... if you use the COPY ONLY option on the NUL backup tests, the test won't interfere with the Point-In-Time backups.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Jeff Moden (5/3/2014)
if you use the COPY ONLY option on the NUL backup tests, the test won't interfere with the Point-In-Time backups.

You mean they won't interfere with differentials 🙂

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

GilaMonster (5/4/2014)
Jeff Moden (5/3/2014)
if you use the COPY ONLY option on the NUL backup tests, the test won't interfere with the Point-In-Time backups.
You mean they won't interfere with differentials 🙂

I LOVE leading statements like that. It means that I'm about to learn something new from my favorite MCM. :w00t:

According to what I've read (the latest version I've read is here...http://technet.microsoft.com/en-us/library/ms191495.aspx ), my interpretation is that if the COPY_ONLY option is on, it won't interfere with either differential backups or PIT log backups. It is, however understood that the COPY_ONLY backup can't actually be used with differentials and that the differentials will still rely on the latest non COPY_ONLY full backup of the database.

Does using a COPY_ONLY backup somehow break the log chain or truncate the log file? If not, I'm not sure what you meant by your statement above. Would you explain, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Jeff Moden (5/4/2014)
my interpretation is that if the COPY_ONLY option is on, it won't interfere with either differential backups or PIT log backups.

Correct.

However, a full backup without COPY_ONLY won't interfere with log backups either. Full and diff backups do not and never have broken the log chain or interfered in any way with log backups, no matter what options are specified. Log backups aren't based on the last full/diff, they're only based on the previous log backup (unless the full/diff was taken after the log chain was broken)

The sole use for COPY_ONLY on a full backup is to prevent that full backup from resetting the differential base, so that any differential is based on the last normal full backup, not the one with COPY_ONLY.

For a more complete coverage: http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 13

I do believe that diff and log backups don't break the log chain but the rely on the LAN from the last full to work don't they? If not, why is a full backup needed to restore a log or differential backup?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

akhamoshioke (5/5/2014)
but the rely on the LAN from the last full to work don't they?

No they don't, as Gail said they're based on the LSN from the last log backup.

akhamoshioke (5/5/2014)[hr If not, why is a full backup needed to restore a log or differential backup?

You need a full backup as this is the base for the database restore. Check books online for a detailed discussion on backup and restore.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 15

akhamoshioke

Ten Centuries

Points: 1110

May 5, 2014 at 7:50 am

#1710706

That was a typo. LSN not LAN. typed from my phone.