backup issue - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

backup issue

Author

Topic

Seanryan
Starting Member

16 Posts

Posted - 2011-08-09 : 11:58:40

Hi,

I have a client that runs a daily "full" backup but when I query msdb.dbo.backupset I see no sign of last backup or any backup - is this possible?

What it means is backups work without error but there are no traces of them ever running in data dictionary.

Any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 12:02:49

They're probably using BackupExec or similar file backup utility, not a native SQL backup or something like Quest LiteSpeed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-09 : 12:35:58

Even backupexec writes to msdb. I am thinking that they are doing file backups rather than SQL backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Seanryan
Starting Member

16 Posts

Posted - 2011-08-09 : 12:58:16

Using standard Maintenance job to backup to disk (later taken to tape) so straight forward auto generated T-SQL scripts. They fire without error and file generates to disk OK. I created a new Maintenance test job to make sure the job was not the issue. Succeeded OK but still no sign of it in msdb.dbo.backupset

Running query

SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

Returns Not yet taken i.e. null value

Checking the properties of the database shows Last Database backup as none + last database log backup as none.

Doesn't make sense as recovery mode seems to be set ok as full.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-09 : 16:37:11

Have you tried querying just msdb.dbo.backupset instead of joining to sys.sysdatabases?

This should work the same, without the additional join:

SELECT database_name, max(backup_finish_date), max(user_name) FROM msdb.dbo.backupset GROUP BY database_name;

If there is nothing in the history at all, I would check for a history cleanup task in the maintenance plans that is deleting history. There could be a task that is misconfigured that is removing history older than a minute instead of keeping history for several weeks or months.

Jeff

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-08-10 : 04:31:10

Hi, you just run this query

SELECT s.database_name, s.backup_start_date, s.backup_finish_date, s.type, mf.physical_device_name
FROM msdb..backupset s
INNER JOIN msdb..backupmediafamily mf ON s.media_set_id=mf.media_set_id
WHERE s.database_name='myDB' and s.backup_start_date between '2011-08-10' and '2011-08-10'
ORDER BY s.backup_start_date

Manju

Seanryan
Starting Member

16 Posts

Posted - 2011-08-10 : 05:06:29

Thanks all.

Tried: SELECT database_name, max(backup_finish_date), max(user_name) FROM msdb.dbo.backupset GROUP BY database_name;

Returns no rows.

Then ran:

SELECT s.database_name, s.backup_start_date, s.backup_finish_date, s.type, mf.physical_device_name
FROM msdb..backupset s
INNER JOIN msdb..backupmediafamily mf ON s.media_set_id=mf.media_set_id
WHERE s.database_name='myDB' and s.backup_start_date between '2011-08-10' and '2011-08-10'
ORDER BY s.backup_start_date

Also returns no rows.

Seanryan
Starting Member

16 Posts

Posted - 2011-08-10 : 05:08:54

Can see a job scheduled weekly to clean up with TSQL of:

declare @dt datetime select @dt = cast(N'2011-07-13T10:06:56' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2011-07-13T10:06:56'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2011-07-13T10:06:56'

But doesn't explain if I run a backup now that it shows no sign in Data dictionary

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-08-10 : 06:10:24

you just check your error log and suspect_pages table

Manju

Seanryan
Starting Member

16 Posts

Posted - 2011-08-10 : 09:39:04

Thanks.

suspect_pages is empty and the system logs show no errors and that backup jobs are successful - so no idea why they log such in the database.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-10 : 10:42:41

Can you run a backup manually and see if it populates? Don't use the maintenance task.

Seanryan
Starting Member

16 Posts

Posted - 2011-08-10 : 13:35:54

ran:

BACKUP DATABASE [wct_cms_test] TO DISK = N'E:\MSSQL\DBackups\wct_cms_test_backup_201108101830.bak' WITH NOFORMAT, NOINIT, NAME = N'wct_cms_test_backup_20110810183040', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'wct_cms_test' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'wct_cms_test' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''wct_cms_test'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'E:\MSSQL\DBackups\wct_cms_test_backup_201108101830.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

result

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 26656 pages for database 'wct_cms_test', file 'WCT_Live_Data' on file 1.
100 percent processed.
Processed 1 pages for database 'wct_cms_test', file 'WCT_Live_Log' on file 1.
BACKUP DATABASE successfully processed 26657 pages in 43.180 seconds (5.057 MB/sec).
The backup set on file 1 is valid.

run (straight away)

SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

and get

AutostoreReportServer Not Yet Taken NA
AutostoreReportServerTempDB Not Yet Taken NA
master Not Yet Taken NA
model Not Yet Taken NA
msdb Not Yet Taken NA
tempdb Not Yet Taken NA
WCT_CMS_Archive Not Yet Taken NA
WCT_CMS_Live Not Yet Taken NA
wct_cms_test Not Yet Taken NA

so backup runs without error from straight TSQL but doesn't record

+ select * from msdb.dbo.backupset

= no rows returned.

Seanryan
Starting Member

16 Posts

Posted - 2011-08-14 : 05:49:24

Still can't find a reason why these backups (any backups) don't log in the database.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-14 : 11:48:27

The only things I can think of are: Are you sure you are connected to the same database system for both operations? Is it possible you are looking at a local instance of SQL Server when querying MSDB - but, you are connected to a server in object explorer?

And, do you have a task in your maintenance plans to cleanup history? That would be the History Cleanup task - and if so, where is that task in the plans and what are it's settings?

jeff

Seanryan
Starting Member

16 Posts

Posted - 2011-08-14 : 14:20:50

All done from the server console. I disabled the "in place" maintenance plans and retested - still no proof of backups (ever) for the database.

This is the maintenance plan:
declare @dt datetime select @dt = cast(N'2011-07-13T10:06:56' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2011-07-13T10:06:56'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2011-07-13T10:06:56'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-14 : 15:57:40

Just in case this is a bug, could you show us the output of SELECT @@VERSION?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-14 : 21:11:33

quote:
Originally posted by Seanryan

All done from the server console. I disabled the "in place" maintenance plans and retested - still no proof of backups (ever) for the database.

This is the maintenance plan:
declare @dt datetime select @dt = cast(N'2011-07-13T10:06:56' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2011-07-13T10:06:56'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2011-07-13T10:06:56'

The commands you have here delete backup history and the maintenance plan history. My guess, without seeing all of the tasks you have in each maintenance plan is that your plan is removing the history as soon as it is created.

What you have in the above is not a backup - or a maintenance plan. It is simply a process that deletes backup history, purges job history and deletes maintenance plan logs.

Now, just because you login to the console of the server does not mean you are accessing the same instance in SSMS. You could be accessing the default instance - but running the backups on a named instance (for example).

Jeff

skybvi
Posting Yak Master

193 Posts

Posted - 2011-08-15 : 10:35:31

run this:--

select * from msdb.dbo.backupset
order by backup_finish_date desc

wat do u get??

Regards,
Sushant
DBA
West Indies

Seanryan
Starting Member

16 Posts

Posted - 2011-08-15 : 15:11:46

get this: (sample of overall)

65631 D14B7770-471C-45BA-AA33-CA490002C8DD 65619 1 1 1 1 1 1 1 NULL 4608 WCT_CMS_Live_backup_20110815200505 NULL WATERFORDCONTAI\cmssqlserver 9 0 5000 4 0 800210000000135800001 800210000000312000001 800210000000135800001 800205000000179300198 2008-09-24 20:34:49.000 2011-08-15 20:05:05.000 2011-08-15 20:05:08.000 L 52 0 90 611 1142272 WCT_CMS_Live WCTMS01 WCTMS01 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 52C660BB-DFCB-4E90-AEA6-7933ED09F37C 52C660BB-DFCB-4E90-AEA6-7933ED09F37C NULL D01D67DD-9305-45BD-9C6D-AD724FE27955 78A9557D-4193-4CA8-8831-761DCCA87F12 NULL NULL
65630 3776C000-CB69-465D-9D03-A5D0559AFF18 65618 1 1 1 1 1 1 1 NULL 4608 WCT_CMS_Live_backup_20110815193502 NULL WATERFORDCONTAI\cmssqlserver 9 0 5000 4 0 800209000000378600001 800210000000135800001 800209000000233900012 800205000000179300198 2008-09-24 20:34:49.000 2011-08-15 19:35:02.000 2011-08-15 19:35:05.000 L 52 0 90 611 1012224 WCT_CMS_Live WCTMS01 WCTMS01 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 52C660BB-DFCB-4E90-AEA6-7933ED09F37C 52C660BB-DFCB-4E90-AEA6-7933ED09F37C NULL D01D67DD-9305-45BD-9C6D-AD724FE27955 78A9557D-4193-4CA8-8831-761DCCA87F12 NULL NULL
65629 33408E10-D2D5-44B2-BD5E-3C56877E9852 65617 1 1 1 1 1 1 1 NULL 4608 WCT_CMS_Live_backup_20110815190502 NULL WATERFORDCONTAI\cmssqlserver 9 0 5000 4 0 800209000000233900001 800209000000378600001 800209000000233900012 800205000000179300198 2008-09-24 20:34:49.000 2011-08-15 19:05:02.000 2011-08-15 19:05:05.000 L 52 0 90 611 1011200 WCT_CMS_Live WCTMS01 WCTMS01 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 52C660BB-DFCB-4E90-AEA6-7933ED09F37C 52C660BB-DFCB-4E90-AEA6-7933ED09F37C NULL D01D67DD-9305-45BD-9C6D-AD724FE27955 78A9557D-4193-4CA8-8831-761DCCA87F12 NULL NULL
65628 673500BD-8E00-401C-A791-7E51F8027EA5 65616 1 1 1 1 1 1 1 NULL 4608 WCT_CMS_Live_backup_20110815183502 NULL WATERFORDCONTAI\cmssqlserver 9 0 5000 4 0 800207000000103300001 800209000000233900001 800207000000103300017 800205000000179300198 2008-09-24 20:34:49.000 2011-08-15 18:35:02.000 2011-08-15 18:35:10.000 L 52 0 90 611 4552192 WCT_CMS_Live WCTMS01 WCTMS01 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 52C660BB-DFCB-4E90-AEA6-7933ED09F37C 52C660BB-DFCB-4E90-AEA6-7933ED09F37C NULL D01D67DD-9305-45BD-9C6D-AD724FE27955 78A9557D-4193-4CA8-8831-761DCCA87F12 NULL NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-15 : 15:16:27

SELECT @@VERSION

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-15 : 16:14:50

So, it does look like you have history available - but the question is why doesn't this show up in your query. Try this query and let's see what shows up:


    Use msdb;

 Select b.server_name
        ,b.database_name
        ,b.name
        ,b.[description]
        ,b.[user_name]
        ,b.backup_start_date
        ,b.backup_finish_date
        ,b.[type]
        ,b.compatibility_level
        ,b.database_version
        ,b.backup_size
        ,b.is_copy_only
   From dbo.backupset b

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources