May 9, 2008 at 9:52 am
I am looking for a script that would:
1) identify all Dbs in full mode and tell me if they all been backed up last night (or when they were backed up last)
2) For all dbs that are not in simple mode check if trn backups run in last hour.
Can anyone help please?
We have separate maintenance plans for Dbs in simple mode and Dbs in full mode, so when a new db is added to the server we might forget to add it to the appropriate maintenance plan. So that is why i would like to have this script.
thank you
May 9, 2008 at 9:56 am
look at these tables in msdb:
backupfile,
backupfilegroup
backupmediafamily
backupmediaset
backupset
May 9, 2008 at 10:30 am
I have checked sys.databases for the list of databases and then looked for backup files in the file system. That way I can be sure the backup occurred and also check for file growth.
Lots of scripts in the scripts section of the site dealing with backups.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 9, 2008 at 11:25 am
Hi,
thank you for your reply
I have over 30 servers that i have to check every day, so i can not go to the backup drives and verify the backups manually. It will be very time consuming. I am looking for a script.
thank you
May 9, 2008 at 12:49 pm
The script you are looking for can be built from the information provided by the two gentlemen above.
Use sys.databases for the database names and recovery. You will want to use msdb..backupset.backup_finish_date and type for the date of the last full/log backup.
Kyle
May 11, 2008 at 10:14 pm
Here's a snippet from a documentation script I have. Note - if a database has never been backed up, it won't appear in this list. That can be your job to figure out, good practice. 🙂
[font="Courier New"]DECLARE @sql nvarchar(4000),
@maxlen1 smallint,
@maxlen2 smallint,
@maxlen3 smallint
IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmp_backups%')
DROP TABLE #tmp_backups
CREATE TABLE #tmp_backups
(
name sysname,
backupfinishdate datetime,
comment varchar(50),
location nvarchar(260)
)
INSERT INTO #tmp_backups
SELECT s.name,
b.backup_finish_date,
CASE WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())
THEN 'Last full backup was within the last 24 hours'
WHEN b.backup_finish_date > DATEADD(dd,-7,getdate())
THEN 'Last full backup was within the last week'
ELSE '***** CHECK BACKUP!!! *****'
END,
bmf.physical_device_name
FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name
INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
WHERE s.name <> 'tempdb'
AND b.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D') -- full database backups only, not log backups
ORDER BY s.name
SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)
FROM #tmp_backups
SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)
FROM #tmp_backups
SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)
FROM #tmp_backups
SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '
SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '
SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '
SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '
SET @sql = @sql + 'FROM #tmp_backups '
SET @sql = @sql + 'ORDER BY name'
EXEC (@sql)
TRUNCATE TABLE #tmp_backups
-- Last log backups
INSERT INTO #tmp_backups
SELECT s.name,
b.backup_finish_date,
CASE WHEN b.backup_finish_date > DATEADD(hh,-12,getdate())
THEN 'Last log backup was within the last 12 hours'
WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())
THEN 'Last log backup was within the last day'
ELSE '***** CHECK BACKUP!!! *****'
END,
bmf.physical_device_name
FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name
INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
WHERE s.name <> 'tempdb'
AND b.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'L') -- log database backups only, not full backups
ORDER BY s.name
SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)
FROM #tmp_backups
SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)
FROM #tmp_backups
SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)
FROM #tmp_backups
SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '
SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '
SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '
SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '
SET @sql = @sql + 'FROM #tmp_backups '
SET @sql = @sql + 'ORDER BY name'
EXEC (@sql)
DROP TABLE #tmp_backups
GO
[/font]
NB - this will only work for SQL Server 2005. If you want something for SQL Server 2000, you've posted in the wrong forum. 😛
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 12, 2008 at 6:42 am
If you are using SQL 2000 the following code should do
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**********************************************************
sp_GetBackupInfo without a parameter will report on all databases for a given server
sp_GetBackupInfo 'hgp' - name of specific single database to be reported
This code found on Sql Server Central posted by someone whose name I have forgotten
**********************************************************/
Create procedure sp_GetBackupInfo
@Name varchar(100) = '%'
--with Encryption
as
set NoCount on
declare
@result int
--try
select
(substring ( database_name, 1, 32)) as Database_Name,
abs(DateDiff(day, GetDate(), backup_finish_date)) as DaysSinceBackup,
backup_finish_date
from msdb.dbo.backupset
where Database_Name like @Name
order by Database_Name, backup_finish_date desc
--finally
SuccessProc:
return 0 /* success */
--except
ErrorProc:
return 1 /* failure */
--end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
May 12, 2008 at 8:44 am
>We have separate maintenance plans for Dbs in simple mode and Dbs in full mode, so when a new db is added to the server we might forget to add it to the appropriate maintenance plan. So that is why i would like to have this script.
Another strategy could be to check for the recovery model in the transaction log backup job. This way you could add new databases that are automatically backed up correctly.
I have a stored procedure that you could help you with this.
http://ola.hallengren.com/sql-server-backup.html
Ola Hallengren
May 12, 2008 at 9:53 am
Ok...this isn't exactly what you are asking, but it does tell you if a backup wasn't done. Just for clarity, I did not write this and it came from another SQL site. Hope this helps.
-- GET DATABASES WITHOUT FULL OR TLOG BACKUPS
-- Louis Nguyen 2008
-- Excludes model and tempdb
-- Will only display if a DB has not been backedup today (day 0) or yesterday (day 1)
-- Disclaimer: This script, is provided for informational purposes only and
-- SQL Server Community (aka: http://WWW.SQLCOMMUNITY.COM) or the author of this
-- script makes no warranties, either express or implied. This script,
-- scenarios and other external web site references, is subject to change
-- without notice. The entire risk of the use or the results of the use of this
-- script remains with the user.
-- dbs with no full backups
if exists (
select *
from master.dbo.sysdatabases a
left join msdb.dbo.backupset b
on a.[name] = b.database_name and datediff(day,b.backup_finish_date,getdate())<2
and b.type='D'
where a.[name] not in ('model','tempdb') and b.database_name is null
) begin
select cast('no full backups' as char(75)) as msg, cast(a.[name] as char(25)) as db_nm
from master.dbo.sysdatabases a
left join msdb.dbo.backupset b
on a.[name] = b.database_name and datediff(day,b.backup_finish_date,getdate())<2
and b.type='D'
where a.[name] not in ('model','tempdb') and b.database_name is null
end
-- dbs with no transaction log backup
if exists (
select *
from
(
select [name] as database_name
from master.dbo.sysdatabases
where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') and [name] not in ('model','tempdb')
) a
left join msdb.dbo.backupset b
on a.database_name = b.database_name and b.type='L' and datediff(day,b.backup_finish_date,getdate())<2
where b.database_name is null
) begin
select cast('no tlog backups' as char(75)) as msg, cast(a.database_name as char(25)) as db_nm
from
(
select [name] as database_name
from master.dbo.sysdatabases
where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') and [name] not in ('model','tempdb')
) a
left join msdb.dbo.backupset b
on a.database_name = b.database_name and b.type='L' and datediff(day,b.backup_finish_date,getdate())<2
where b.database_name is null
end
May 20, 2008 at 10:50 pm
NB - this will only work for SQL Server 2005. If you want something for SQL Server 2000, you've posted in the wrong forum. 😛
If change "master.sys.databases" to "master.dbo.sysdatabases", it seems work for SQL2000. Hope I am not missing anything here. 😉
May 21, 2008 at 11:51 am
Everybody! thank you very much for your help! I was able to do what i needed.
thank you!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply