why are my differential backups 0kb? 1

[kim20]

Hello

I have my backup strategy for database internet
full weekly backup
daily transaction log backups (every half hour between 07:00 and 21:00)
daily differentials (3 times during timeframe as above)
although not live yet the database has had occassions of data updates/inserts/deletes - i completed a test restore successfully.

there are some trans logs at 606kb, 197kb - the standard is 92kb - but the diff backups are always 0kb ?

is this expected? i understand it reflects the changes since the last full backup but i expected at least a diff with 10/20 kb certainly the time data has been updated ?

 

[mrdenny]

The diff should have something in it. Are you sure that the backup is successful?

What's in the ERRORLOG and application log?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kim20]

Hi

yes running successfully..............

SQL Server log message

Database differential changes backed up: Database: Internet, creation date(time): 2007/04/03(12:38:41), pages dumped: 3652, first LSN: 3965:637:1, last LSN: 3965:639:1, full backup LSN: 3930:62:3, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\etc\etc\Diff\Internet_diff_2007-07-05T13:01.BAK'}).

Executed as user: sqlsrvracc. 12 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000] (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50 percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up. [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000] (Message 3211) 80 percent backed up. [SQLSTATE 01000] (Message 3211) 90 percent backed up. [SQLSTATE 01000] (Message 3211) Processed 3632 pages for database 'Internet', file 'bourn_dat' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent backed up. [SQLSTATE 01000] (Message 3211) Processed 1 pages for database 'Internet', file 'bourn_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE WITH DIFFERENTIAL successfully processed 3633 pages in 7.357 seconds (4.044 MB/sec). [SQLSTATE 01000] (Message 3014). The step succeeded.

actual code below run as job (T-SQL Master DB)

declare @date varchar(16)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)
set @BackupPath = 'D:\etc\etc\Diff\Internet_diff'
set @date = convert(varchar(16), getdate(), 126)
set @BackupFile = @BackupPath + '_' + @date + '.BAK'
BACKUP DATABASE [Internet] TO  DISK=@BackupFile WITH  NOINIT ,  NOUNLOAD ,  DIFFERENTIAL ,  NAME = N'Internet backup',  NOSKIP ,  STATS = 10,  NOFORMAT

errrrr.............why is the actual filename only
Internet_diff_2007-07-03T21 ?
where is the .bak ?

is it the colon messing things up ?

whoops think it might be, just got a diff with 28.5MB..................fielname as
Internet_diff_2007-07-06T08.BAK
i changed convert(16) to convert(13) - is there a better way of saying
convert(varchar(13), getdate(), 126) to trim colons and hyphens out but retain date and time (hour only will do) ?

thank you very much

 

[mrdenny]

Not in a single command.

set @date = convert(varchar(10), getdate(), 112) + '_' + convert(varchar(2), datepart(hh, getdate()))
set @BackupFile = @BackupPath + '_' + @date + '.BAK'

Apparently those colons can be a big problem. It would be nice if it told you there was a problem instead of assuming that all went well.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com