August 15, 2017 at 11:44 am
I have a DB which is supposed to be close to 250 GB in Size. I have couple of scripts which are running every day. I have scheduled Full weekly backup + Daily diff. The problem is that the backup file from this Saturday is close to 17GB and the db size is 247 GB in size. What am I missing in the script? Why it is not taking the full backup? If it is then how come the backup size is smaller?
1st script which executes a different SPALTER PROCEDURE [dbo].[Full_backup_databases]
@diff_only tinyint = 0, /* 0=no, 1=yes */
@full_only tinyint = 0, /* 0=no, 1=yes */
@copy_only tinyint = 0, /* 0=no, 1=yes */
@folder nvarchar(2048) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @name SYSNAME
-- Insensitive to ensure we get entire list (see http://stackoverflow.com/questions/4048147/inconsistent-cursor-results-when-looping-over-databases)
DECLARE dbcursor INSENSITIVE CURSOR FOR
SELECT name
FROM sys.databases
WHERE STATE = 0 --Online
AND name not in ('tempdb','THINK','RBCS','FarmReach_Test_06-30-2017','ReportServerTempDB')
AND source_database_id IS NULL
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC backup_database @dbName=@name, @diff_only=@diff_only, @full_only=@full_only, @copy_only=@copy_only
FETCH NEXT FROM dbcursor INTO @name
END;
CLOSE dbcursor
DEALLOCATE dbcursor
END
2nd script Takes a Full backup of a DB.ALTER PROCEDURE [dbo].[backup_database]
-- Add the parameters for the stored procedure here
@dbName sysname = null,
@folder nvarchar(2048) = null,
@diff_only tinyint = 0, -- 1 = yes, 0=no
@full_only tinyint = 0, /* 0=no, 1=yes */
@copy_only tinyint = 0 -- 1=yes, 0=no
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @path nvarchar(2048)
declare @now datetime
declare @sql nvarchar(max)
set @now = getdate()
if @folder is null
begin
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
N'BackupDirectory',
@folder output;
end;
set @path = @folder+'\'+QUOTENAME(@dbName)+'\'
+RIGHT('0000'+CONVERT(nvarchar,DATEPART(yyyy,@now)),4)
+RIGHT('00'+CONVERT(nvarchar,DATEPART(ww,@now)),2)
exec master.sys.xp_create_subdir @path
set @sql = 'BACKUP DATABASE '+QUOTENAME(@dbName)+' TO DISK='''+@path+'\'
+QUOTENAME(@dbName)+'_'+CONVERT(nvarchar, @now, 112)+'_'
+RIGHT('0000'+CONVERT(nvarchar, DATEPART(hh,@now)),2)
+RIGHT('00'+CONVERT(nvarchar, DATEPART(mi, @now)),2)
+RIGHT('00'+CONVERT(nvarchar, DATEPART(ss,@now)),2)
+CASE WHEN @diff_only = 1 THEN N'.dif' ELSE N'.bak' END
+''' with stats=10'
-- Can't do a differential on master db
if @diff_only = 1 and UPPER(@dbName) <> N'MASTER' AND @full_only = 0
begin
set @sql = @sql + ', DIFFERENTIAL'
end;
if @copy_only = 1
begin
set @sql = @sql + ', copy_only'
end;
exec(@sql)
END
3rd one for Diff Backup.USE [CommonDB]
GO
/****** Object: StoredProcedure [dbo].[Diff_backup_databases] Script Date: 8/15/2017 12:37:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================================================
-- Differential backup of all databases
-- Description: Takes a differential backup for each database.
-- If a valid full backup is not found, then one is taken instead.
-- ===================================================================================
ALTER PROCEDURE [dbo].[Diff_backup_databases]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets FROM
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @folder nvarchar(2048)
DECLARE @file nvarchar(2048)
DECLARE @instanceBackupDir nvarchar(2048)
DECLARE @backup_exists int
DECLARE @name sysname
DECLARE @date_backup_start datetime
DECLARE @date_database_create datetime
-- Determine the instance's backup directory
EXEC [master].[dbo].xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory',
@instanceBackupDir OUTPUT
-- Insensitive to ensure we get entire list (see http://stackoverflow.com/questions/4048147/inconsistent-CURSOR-results-when-looping-over-databases)
DECLARE dbcursor INSENSITIVE CURSOR FOR
SELECT name
FROM sys.databases
WHERE name not in ('tempdb','THINK','RBCS','FarmReach_Test_06-30-2017','ReportServerTempDB')
AND source_database_id IS NULL
AND state = 0
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
-- Store the path of the newest full backup for @name into @file
SELECT @file=physical_device_name
FROM [msdb].[dbo].backupmediafamily mf
INNER JOIN [msdb].[dbo].backupset b ON mf.media_set_id = b.media_set_id
WHERE b.database_name = @name
AND b.backup_start_date = ( SELECT MAX(backup_start_date)
FROM [msdb].[dbo].backupset b2
WHERE b.database_name = b2.database_name AND b2.type = 'D')
-- Check if @file actually exists
EXEC [master].[dbo].xp_fileexist @file, @backup_exists OUTPUT
SELECT @date_database_create=a.create_date, @date_backup_start=MAX(b.backup_start_date)
FROM sys.databases a
JOIN msdb.dbo.backupset b ON a.name = b.database_name
WHERE a.name = @name
GROUP BY a.create_date
IF (@backup_exists = 1)
-- Make sure a full backup is in the instance's backup directory and valid
IF ((SELECT CHARINDEX (@instanceBackupDir , @file, 0)) <> 1)
OR (DATEDIFF(day, @date_backup_start, @date_database_create) >= 0)
EXEC backup_database @dbName=@name, @diff_only=0, @copy_only=0
-- PRINT 'FULL BACKUP '+ @name + ' --> EXEC backup_database @dbName=' + @name + ', @diff_only=0, @copy_only=0'
ELSE
EXEC backup_database @dbName=@name, @diff_only=1, @copy_only=0
-- PRINT 'DIFF BACKUP '+ @name + ' --> EXEC backup_database @dbName=' + @name + ', @diff_only=1, @copy_only=0'
ELSE
EXEC backup_database @dbName=@name, @diff_only=0, @copy_only=0
-- PRINT 'FULL BACKUP '+ @name + ' --> EXEC backup_database @dbName=' + @name + ', @diff_only=0, @copy_only=0'
SET @file = null
FETCH NEXT FROM dbcursor INTO @name
END;
CLOSE dbcursor
DEALLOCATE dbcursor
END
August 15, 2017 at 12:14 pm
Do you have backup compression turned on by default? You can check that like this:SELECT name, value_in_use FROM sys.configurations WHERE name = 'backup compression default'
August 15, 2017 at 12:15 pm
Well, let us see. All you provided was the scripts (stored procedures). Not really much we can tell from these alone. Nothing tells us how these were invoked at the time of your "problem" with the backup.
If you are curious as to what type of backup was taken and when, check the appropriate tables in the msdb database, that will tell you if it was a differential or full backup. If I remember right, there is other information that the tables will tell you also.
August 15, 2017 at 12:30 pm
The backup usually is smaller as it won't be backing up unallocated space. Backup compression that Chris already mentioned can also result in the backup being much smaller.
Without any type of compression, the backup size is often pretty close to the reserved space if you execute sp_spaceused for the database.
Sue
August 15, 2017 at 12:37 pm
Chris Harshman - Tuesday, August 15, 2017 12:14 PMDo you have backup compression turned on by default? You can check that like this:SELECT name, value_in_use FROM sys.configurations WHERE name = 'backup compression default'
backup compression default 1
August 15, 2017 at 1:22 pm
I took the backup which was 17GB in size, restored it on a test server and now the DB size is 247GB. I didn't know the Microsoft guarantees 90%+ data compression.
August 15, 2017 at 2:03 pm
newdba2017 - Tuesday, August 15, 2017 1:22 PMI took the backup which was 17GB in size, restored it on a test server and now the DB size is 247GB. I didn't know the Microsoft guarantees 90%+ data compression.
The actual rate of data compression in a backup will depend on your data. Here's some info about it:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server
You can see the backup history information as Lynn mentioned with a query like this:SELECT bs.backup_set_id, bs.database_name, CASE bs.type WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'Filegroup' WHEN 'G' THEN 'Diff Filegroup' ELSE bs.type END AS backup_type,
bs.is_copy_only AS is_copy, bs.backup_start_date, bs.backup_finish_date, DateDiff(minute, bs.backup_start_date, bs.backup_finish_date) AS backup_min,
bs.name, bs.description, mf.physical_device_name, bs.user_name, bs.backup_size, bs.compressed_backup_size, bs.first_lsn, bs.last_lsn, bs.checkpoint_lsn, bs.database_backup_lsn
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily mf ON bs.media_set_id = mf.media_set_id
WHERE bs.database_name = 'yourDBname'
ORDER BY bs.backup_set_id desc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply