maintenance plan doubt - 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

General SQL Server Forums

New to SQL Server Administration

maintenance plan doubt

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-05-07 : 15:35:59

Hi Experts,
in my environment, in a server, around 30 databases are there. some are in maintenance plan and some are logshipped. i'd like to know which databases are not in maintenance plan. (i mean which databases are not backing up)

Thanks in advance.

Arnav
Even you learn 1%, Learn it with 100% confidence.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 15:38:43

If you are using SQL 2005 or higher you can try this script(Source : MSDN)

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

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2010-05-07 : 15:57:18

Thanks you Bohra....
it worked for me

Arnav
Even you learn 1%, Learn it with 100% confidence.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 16:09:09

You are welcome.. Glad to help.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources