May 10, 2012 at 5:43 am
I hope I am posting this in the correct place. I have been doing a lot of reading until now. 🙂
I just started a new job last week. I am now focusing on being a full time DBA, instead of being a network admin type. I have a pretty solid understanding of SQL but have run into something probably pretty basic that I am just missing.
We have a Maintenance job that has been working fine that was doing a Full backup once daily of a particular database. It was also backing up the TLog at the same time.
I have made a change to start backing up the TLOG hourly during business hours. I also have added a step to do a subplan step to do a differential of the DB 3 times during business hours. This is what is "failing" it is telling me that I need to do a full backup first. I thought that was what is happening at 2 am. ?? Am I missing something simple here? We are on SQL 2008R2.
Thanks.
Tim
May 10, 2012 at 6:08 am
To take differential backup, we need the Full backup in the place, else it will fail.
So make your schedule in such a way, First let it happen full backup for all the user databases then you can go ahead and do differential from that time on wards.
May 10, 2012 at 6:12 am
That is what I am doing, or at least I think I am. The maint. plan has a subplan step #1 that runs at 2 am, it has the task in it to do a Full DB back up of the database in question.
I then added yesterday a seperate subplan step #4, that has just the task of doing a differential backup of the same DB but starting at 6 am (before employees start coming into the main office) and then running every 6 hours (so 6 am, noon and then ending at 6 pm).
I would have thought, that would have been it. I have the Full at 2 am and then the first differential at 6 am. But that does not seem to be the case.
May 10, 2012 at 6:18 am
Is there any NEW DB created after maintennace plan? Which plan directly wants to take DIff?
Or Do you receving specific error when ever job runs in the intervals.
Please paste error.
May 10, 2012 at 6:19 am
TimParker (5/10/2012)
I hope I am posting this in the correct place. I have been doing a lot of reading until now. 🙂I just started a new job last week. I am now focusing on being a full time DBA, instead of being a network admin type. I have a pretty solid understanding of SQL but have run into something probably pretty basic that I am just missing.
We have a Maintenance job that has been working fine that was doing a Full backup once daily of a particular database. It was also backing up the TLog at the same time.
I have made a change to start backing up the TLOG hourly during business hours. I also have added a step to do a subplan step to do a differential of the DB 3 times during business hours. This is what is "failing" it is telling me that I need to do a full backup first. I thought that was what is happening at 2 am. ?? Am I missing something simple here? We are on SQL 2008R2.
Thanks.
Tim
There must be some database which has not been backed up since it was created. Execute the below provided query & see is you get NULL as the LastBackupDate for any database.
SELECT D.[name] AS DatabaseName,MAX(BS.[backup_finish_date]) AS LasBackupDate
FROM sys.databases D LEFT OUTER JOIN msdb.dbo.backupset BS
ON
D.[name]=BS.[database_name]
WHERE
D.[name] NOT IN ('master','model','msdb','tempdb')
GROUP BY D.[name]
If you get NULL in the LastBackupDate column then you first need to take a full backup of that partcular database.
May 10, 2012 at 6:21 am
Here is the error from the Log;
Executing the query "BACKUP DATABASE [HealthLinx2010SQL] TO DISK = N'E..." failed with the following error: "Cannot perform a differential backup for database "HealthLinx2010SQL", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The only step after the backup of this DB currently is a "Maintenance Cleanup Task" that cleans up DB Backup Files older than 5 Days.
May 10, 2012 at 6:24 am
I just ran the query and on this particular server there are 3 Databases. Two of which are not even currently being used (they are older versions of the production one)
they all have dates, the one that I am trying to do the differential on has currently todays date at 8:00 am which would be the last TLog that would have just happened.
May 10, 2012 at 6:31 am
TimParker (5/10/2012)
I just ran the query and on this particular server there are 3 Databases. Two of which are not even currently being used (they are older versions of the production one)they all have dates, the one that I am trying to do the differential on has currently todays date at 8:00 am which would be the last TLog that would have just happened.
In that case you should be able to take the differential backup without any problem. Are you able to take the differential backup manually for that particular database?
BACKUP DATABASE YourDatabaseName To DISK = 'Drive:\BackupDirectory\YourDatabaseName.BAK'
WITH DIFFERENTIAL,STATS=1
May 10, 2012 at 6:36 am
Please check whether the full backup of 'HealthLinx2010SQL' is actually a clean backup or not, i mean completed properly. A valid file.
May 10, 2012 at 6:39 am
I just tried to run it manually, hadn't done that yet....and got the same error.
As far as I can tell, it is a complete good backup, according to the logs. But I am starting to doubt it now....as this shouldnt' be that hard.
Is there something specific I need to look at in regards to the file to make sure it is good?
May 10, 2012 at 6:43 am
TimParker (5/10/2012)
I just tried to run it manually, hadn't done that yet....and got the same error.As far as I can tell, it is a complete good backup, according to the logs. But I am starting to doubt it now....as this shouldnt' be that hard.
Is there something specific I need to look at in regards to the file to make sure it is good?
1. If it is not a very big database then you can try to restore it on some test server.
2. If the database is huge in size then try using:
RESTORE VERIFYONLY FROM DISK = 'Drive:\BackupDirectory\BackupFileName.BAK'
WITH STATS =1
May 10, 2012 at 6:49 am
RESTORE VERIFYONLY
May 10, 2012 at 6:52 am
I am moving a copy of the backup file to another server and am going to attempt a restore there to see if the file is good in that regard.
Just saw your replies. Whoops. Trying that now. It is a 4GB DB currently.
May 10, 2012 at 7:06 am
I used the restore verifyonly and got "The backup set on file 1 is valid."
So I think I have a complete, valid file as the logs would indicate. Next step?
May 10, 2012 at 7:17 am
Select distinct top 1
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
b.backup_finish_date as LastBackup,
d.dbid 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
from sys.sysdatabases d inner join msdb.dbo.backupset b
On b.database_name =d.name where d.dbid=7 order by b.backup_finish_date desc
Do you see the backup finish time correct there for full backup. Just put the correct dbid in where condition.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply