Backup script giving odd output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Backup script giving odd output

Author

Topic

ferretneck
Starting Member

11 Posts

Posted - 2012-01-13 : 06:52:04

Hi all

I have a backup script (below) which runs through all my databases nightly and backs them up to disk.

The weird thing is, in my list of backups for one database, all the file sizes are different. And I'm not talking they increase by a couple of mb per day, I'm saying that one day the size can be 100mb, the next day 17mb, then the next day 170mb.

WTF?


DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'B:\Database\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + REPLACE(REPLACE(@name, '_', '\'), '-', '\') + '\' + @name + '_' + @fileDate + '.BAK' 
       print @fileName
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Thanks

Ben

Kristen
Test

22859 Posts

Posted - 2012-01-13 : 07:32:05

"one day the size can be 100mb, the next day 17mb, then the next day 170mb."

Full backup will include enough of the Log File to get a consistent Restore. Maybe your log file has lots of "open" transactions at the times when you have large BAK files?

Other option is that something is purging data?

Other option is that an Index Rebuild has "tidied up" the structure of the Index pages, and there are fewer physical pages that are in use, and thus fewer pages to be backed up. But the difference between 17MB and 170MB is a lot of "slack space" in LOTS of pages !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources