March 3, 2011 at 7:31 am
Hi,
I've got a strange problem at one of my clients sites.
I'm trying to take a differential backup but it can't seem to find the full backup taken previoulsy.
A few weeks ago their internal IT were taking a backup in between us taking our and the differential but hey then moved the backup file off the server, hence the path was broken.
They have stopped doing that backup now but the problem persists.
Details of my investigation are:
The Error:
Cannot perform a differential backup for database "XXX_MSCRM", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The Investigation:
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
ELSE msdb..backupset.type
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
AND database_name='XXX_MSCRM'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
The Results:
database_name backup_start_date backup_finish_date expiration_date backup_type backup_size logical_device_name physical_device_name backupset_name description
XXX_MSCRM 27/02/2011 01:00 27/02/2011 01:15 Database 8657497088 F:\Backup\XXX_MSCRM\XXX_MSCRM_Full_B.bak
XXX_MSCRM 27/02/2011 01:15 27/02/2011 01:15 Diff 9760768 F:\Backup\XXX_MSCRM\XXX_MSCRM_Diff_B.bak
XXX_MSCRM 27/02/2011 03:30 27/02/2011 03:31 Database 7168 {0D6484A8-C897-499E-923F-ECB90C230A3C}8
XXX_MSCRM 27/02/2011 04:00 27/02/2011 04:00 Diff 7663616 F:\Backup\XXX_MSCRM\XXX_MSCRM_Diff_B.bak
XXX_MSCRM 28/02/2011 03:30 28/02/2011 03:30 Database 7168 {B94CC9EA-33C0-437E-B940-2ED98B76392A}8
XXX_MSCRM 28/02/2011 04:00 28/02/2011 04:00 Diff 5566464 F:\Backup\XXX_MSCRM\XXX_MSCRM_Diff_B.bak
XXX_MSCRM 28/02/2011 19:01 28/02/2011 19:02 Database 7168 {106C22BE-E67A-4797-8E42-66CB11C02A21}9
XXX_MSCRM 01/03/2011 03:30 01/03/2011 03:30 Database 7168 {0709620A-3DF1-4F7E-9BE7-FE2D7E594148}8
XXX_MSCRM 01/03/2011 19:09 01/03/2011 19:09 Database 7168 {9777F933-0558-41C3-BB14-5D84AE677951}9
My Question:
As you can see, there are many database backups to GUID (which I believe is a virtaul deveice) but the size of them is always 7168.
It is still affecting my Differentails because as you can see between the 27th and 28th the differnetial size decreases, which would surely only happen if there were a full backup between them.
Any guidance is appreciated.
Giles
originally posted incorrectly on SQL Server 7 & 2000 as http://www.sqlservercentral.com/Forums/Topic1071830-24-1.aspx
March 3, 2011 at 7:37 am
I don't know the problem, but looks like it would be a good idea to take fresh full backup now.
March 3, 2011 at 7:46 am
Agreed, as soon as we found the issue we reverted back to full backups every day. The only issue is we need to keep a fortnights worth of backups and it's eating up space.
March 3, 2011 at 7:54 am
Can you take a differential now after your new full backup ?
March 3, 2011 at 8:20 am
Yes, the diff backup works fine following a full backup. Tthe problem is only if one of those 7168 sized backup has happened in between.
Their IT support say they've stopped their 3rd party backup software and 7168 is a strange size for the backup file of this particular DB.
So I need to know what 7168 represents so we can hunt it down and stop it.
March 3, 2011 at 9:54 am
Any useful details in the SQL Server log? One suggestion would be to use Profiler to trace the events at around 03:30.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 3, 2011 at 10:21 am
Good call, I've either been lazy, forgetful or both but I should have looked there earlier.
Found the event straight away and it is backing up to a virtual device.
Database backed up. Database: xxx_MSCRM, creation date(time): 2009/07/08(16:26:21), pages dumped: 1, first LSN: 158350:5502:190, last LSN: 158350:5581:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{4E5BC8F3-CB58-4C32-8C52-3DBC6C24DAF0}8'}). This is an informational message only. No user action is required.
I've got to send that through to their internal IT and ask them to stop it (as agreed in their DR strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply