-----------------------------------
------- Backup Tune-up ------------
-----------------------------------

BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AW.bak'
WITH init, format

/*
After doing just a little bit of backup tuning you start to get a feel for what's right and what's wrong.
I have no idea from one system to another what kind of perf to expect out of an operation, but I know
what not to expect.  Backing up 1TB in 4+hrs is very bad.  I've even see 1TB backed up 
*/
-- Backup DB.
BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AW1.bak'
,DISK = 'c:\AW2.bak'
WITH init, FORMAT -- you should always use these.

sp_configure 'backup compression default', 1

--You can use as many files as your box can stand... up to 64.
BACKUP DATABASE [AdventureWorks]
TO 
DISK = 'c:\mYbACKUPS\AW1.bak',
DISK = 'c:\mYbACKUPS\AW2.bak',
DISK = 'c:\mYbACKUPS\AW3.bak',
DISK = 'c:\mYbACKUPS\AW4.bak',
DISK = 'c:\mYbACKUPS\AW5.bak',
DISK = 'c:\mYbACKUPS\AW6.bak'
WITH INIT, FORMAT,
--BUFFERCOUNT = 500,
MAXTRANSFERSIZE = 2097152,
COMPRESSION

---- Restore DB.
-------What's wrong with it?  Why's it taking so long?
DROP DATABASE AdventureWorks

DBCC TRACEON(1806, 3605, 3004) -- 
RESTORE DATABASE AdventureWorks
FROM DISK = 'c:\AW.bak'
-- ~2.27

---- Restore DB again.
--1st drop DB to even the playing field.
DROP DATABASE AdventureWorks

DBCC TRACEOFF(1806) 
DBCC TRACEON(3605, 3004)
RESTORE DATABASE AdventureWorks
FROM DISK = 'c:\AW.bak'
--~1.39

--***************************************************************
--What's the problem here?
--***************************************************************














































-- Instant File Initialization (IFI) is the culprit here.  You need to turn it on if you can.
-- Once you give Perform Volume Maintenance Tasks to the service acct and burp SQL it'll work.
-- There's no way to turn it on in SQL, but you can turn it off temporarily using T1806.

--Now investigate some of these on their own by using 3604.
-- Clear previous flags.
DBCC TRACEOFF(1806, 3004, 3213, 3014, 3226, 3001)

dbcc traceon(3004, 3604) --vastly diff info that was put in log.

BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AW1.bak'
WITH init, FORMAT

RESTORE DATABASE AdventureWorks
FROM DISK = 'c:\AW.bak'
WITH buffercount = 50,
REPLACE
dbcc traceoff(3004, 3604)
-------------------------------------
dbcc traceon(3604, 3213) -- Buffer info.
--take special not of alignment... this is partition alignment.
--don't take for granted that san/windows guys know about this.
--win2k3 doesn't do it and neither does connecting a san to win2k8 box.
BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AW1.bak'
WITH init, FORMAT

RESTORE DATABASE AdventureWorks
FROM DISK = 'c:\AW.bak'
WITH buffercount = 50,
REPLACE
dbcc traceoff(3213)
-------------------------------------
dbcc traceon(3604, 3001) -- Don't log to msdb.
BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AW1.bak'
WITH init, FORMAT
dbcc traceoff(3001)
-------------------------------------
dbcc traceon(3604, 3014) -- More info.
BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AW1.bak'
WITH init, FORMAT
dbcc traceoff(3014)
-------------------------------------



----Monitor compression stats------
-----------------------------------
USE msdb
SELECT 
  b.database_name 'Database Name', 
  CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)', 
  CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)', 
  CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) / 
  CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio', 
  CONVERT(numeric (20,1), 100-((b.compressed_backup_size/b.backup_size)*100)) AS 'Compression%',
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)' 
FROM 
  msdb.dbo.backupset b 
WHERE 
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0 
  AND b.backup_size > 0 
ORDER BY 
  b.backup_finish_date DESC

--To tune a backup
-----Start by baking up to NUL---

BACKUP DATABASE AdventureWorks
TO DISK = 'NUL'

--Now test diff. buffers.
BACKUP DATABASE AdventureWorks
TO DISK = 'NUL'
WITH BUFFERCOUNT = 25

--You can check these counters:
--Disk Read Bytes/sec -- how fast you're reading the disk.
--Avg. Disk sec/Read -- your latency.
--So if the 1st counter levels and the other one continues to climb, then I/O is your latency.

--Once you land on a sweet spot for reads, you can add in the real output device.
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.bak'
WITH init, format

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.bak'
WITH BUFFERCOUNT = 25

--Remember, on busy systems, you can easily do too much.
--if your perf starts to suffer, then back-off on the MaxTSize.
--MaxTSize is 2ndary.  I've seen it help, but due to it's contiguous nature it can hurt perf.
--You'll get more bang out of buffers, and files.
--To see how many buffers you'll use, buffercount * maxTsize.