We used to take backups of databases on all our systems every night for a long time. That meant we backuped up 12TB of data every day for about 2 weeks. Since we delted backups older than that. Imagine how much space that took. Getting the databases back from tapes was another job.
So recently at our company, they set up full back up once a week (with compression) and differential backups once a day. Since the database were in simple mode, a point of time recovery was not possible. But we could always bring the database upto the latest differential back up and use flat files of data that we had daily to bring it up to date. With a full back per week with compression :a 160GB database went down to nearly 40GB. So considering an approximate factor of four (not exactly). The total backup went down to about 3TB. And the differential backups came to hardly 70GB for a week. We too deleted any full and differential backups more than two weeks old.
This greatly solved our space problem since,
we now required about
3*2(once every week)tb + 70*2 GB = 6TB(approx) to back up 2 weeks worth of data,
With easy recovery, since they were on the same box as the online databases.
It also meant transferring the dbs much more faster across our produciton and dev/qa environments.
as opposed to:
12*14 = 168TB!
The things we needed to be careful about was:
To use backup with copy only option, incase we needed to copy most current data from production. So as to not throw the backup process out of sync by taking full backups manually midday.
Full backup code:
Declare @vr_path VARCHAR(1000),
@UseTimeInFileName Char(1) = 'N'
DECLARE @vr_dbname VARCHAR(100)
DECLARE @SQL VARCHAR(MAX)
DECLARE @BackupTime VARCHAR(50)
IF @UseTimeInFileName = 'Y'
BEGIN
SET @BackupTime = convert(varchar(8),getdate(),112) + '_' + replace(convert(varchar(8),getdate(),108),':','')
END
ELSE
BEGIN
SET @BackupTime = convert(varchar(8),getdate(),112)
END
SET @SQL = ''
SELECT @SQL = @SQL + 'BACKUP DATABASE [' + name + '] TO DISK = ' + Char(39) +
@vr_path + name
+ '_' + @BackupTime + '.bak'+ Char(39) + ' WITH INIT, COMPRESSION;'
FROM sys.databases
WHERE name not in ('tempdb')
AND state = 0 -- Online Databases Only
AND source_database_id IS NULL --Not a database snapshot
AND is_in_standby = 0
PRINT @SQL
EXEC (@SQL)
Differential backup code:
Declare @vr_path VARCHAR(1000),
@UseTimeInFileName Char(1) = 'N'
DECLARE @vr_dbname VARCHAR(100)
DECLARE @SQL VARCHAR(MAX)
DECLARE @BackupTime VARCHAR(50)
IF @UseTimeInFileName = 'Y'
BEGIN
SET @BackupTime = convert(varchar(8),getdate(),112) + '_' + replace(convert(varchar(8),getdate(),108),':','')
END
ELSE
BEGIN
SET @BackupTime = convert(varchar(8),getdate(),112)
END
SET @SQL = ''
SELECT @SQL = @SQL + 'BACKUP DATABASE [' + name + '] TO DISK = ' + Char(39) +
@vr_path + name
+ '_' + @BackupTime + '.bak'+ Char(39) + ' WITH DIFFERENTIAL, COMPRESSION;'
FROM sys.databases
WHERE name not in ('tempdb', 'master')
AND state = 0 -- Online Databases Only
AND source_database_id IS NULL --Not a database snapshot
AND is_in_standby = 0
PRINT @SQL
EXEC (@SQL)
