August 7, 2018 at 11:22 am
I'm an Oracle DBA that used to work with MSSQL a few years ago, and now find myself taking over admin duties for a large number of MSSQL databases, from version 2008 & up.
I am putting together a report for management showing all the databases and their current status, from which I will make recommendations on how best to proceed with backup strategies etc.
From searches I have found / cobbled together a query to show me database size, status, recovery mode and last backup for all the databases on a given server, but I would like to add backup size as well so I can prioritize by size to see which, if any I will possibly implement incremental backups for.
This is the query I am running:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_desc
I'd like to add backup size to this query, but am not familiar enough with MSSQL data dictionary to easily find out how, (oracle is my main DBA expertise) so hoping someone here can point me in right direction.
I'm not even sure if I'm using the best system view(s) to accomplish what I want here.
This query gives me too many rows, and if I use a MAX function it still doesn't look right :
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc,bus.backup_size AS BackupSize
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_desc, bus.backup_size
Getting database size would probably also suffice now that I think about it, since it will also give me what I am looking for, but last backup size would be nice.
Thanks in advance..
August 7, 2018 at 12:04 pm
Glen_A - Tuesday, August 7, 2018 11:22 AMI'm an Oracle DBA that used to work with MSSQL a few years ago, and now find myself taking over admin duties for a large number of MSSQL databases, from version 2008 & up.I am putting together a report for management showing all the databases and their current status, from which I will make recommendations on how best to proceed with backup strategies etc.
From searches I have found / cobbled together a query to show me database size, status, recovery mode and last backup for all the databases on a given server, but I would like to add backup size as well so I can prioritize by size to see which, if any I will possibly implement incremental backups for.
This is the query I am running:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_descI'd like to add backup size to this query, but am not familiar enough with MSSQL data dictionary to easily find out how, (oracle is my main DBA expertise) so hoping someone here can point me in right direction.
I'm not even sure if I'm using the best system view(s) to accomplish what I want here.
This query gives me too many rows, and if I use a MAX function it still doesn't look right :
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc,bus.backup_size AS BackupSize
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_desc, bus.backup_sizeGetting database size would probably also suffice now that I think about it, since it will also give me what I am looking for, but last backup size would be nice.
Thanks in advance..
Table msdb.dbo.backupset includes the backup file size and the compressed backup file size (if applicable).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 7, 2018 at 2:47 pm
Glen_A - Tuesday, August 7, 2018 11:22 AMI'm an Oracle DBA that used to work with MSSQL a few years ago, and now find myself taking over admin duties for a large number of MSSQL databases, from version 2008 & up.I am putting together a report for management showing all the databases and their current status, from which I will make recommendations on how best to proceed with backup strategies etc.
From searches I have found / cobbled together a query to show me database size, status, recovery mode and last backup for all the databases on a given server, but I would like to add backup size as well so I can prioritize by size to see which, if any I will possibly implement incremental backups for.
This is the query I am running:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_descI'd like to add backup size to this query, but am not familiar enough with MSSQL data dictionary to easily find out how, (oracle is my main DBA expertise) so hoping someone here can point me in right direction.
I'm not even sure if I'm using the best system view(s) to accomplish what I want here.
This query gives me too many rows, and if I use a MAX function it still doesn't look right :
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc,bus.backup_size AS BackupSize
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_desc, bus.backup_sizeGetting database size would probably also suffice now that I think about it, since it will also give me what I am looking for, but last backup size would be nice.
Thanks in advance..
I used your query and you can add more into your query but you may want to start using a CTE to get some of this. It sounds like you are looking for something along the lines of :WITH Backups (DatabaseName, LastBackUpTime, recovery_model, state_desc, BackupSize, DatabaseSize, rn)
AS
(
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), bus.backup_finish_date, 101),'-') AS LastBackUpTime,
sdb.recovery_model_desc,
sdb.state_desc,
bus.backup_size/1024/1024 AS BackupSize,
CAST(SUM(mf.size) * 8. / 1024 AS DECIMAL(8,2)) as DatabaseSize,
row_number() over(partition by sdb.name order by backup_finish_date desc) as rn
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.name
INNER JOIN sys.master_files mf
on sdb.database_id = mf.database_id
where sdb.name not in ('master','model','msdb','tempdb')
Group by sdb.Name, bus.backup_finish_date, recovery_model_desc, bus.backup_size, sdb.state_desc
)
SELECT
DatabaseName,
LastBackupTime,
recovery_model,
state_desc,
BackupSize,
DatabaseSize
FROM Backups
WHERE rn = 1
Some other things to consider -
You may also want to include backup type or filter it to just full backups. As it is now, it would include any backup.
Hopefully no one is backing up to NUL but those would be included as well. Not sure what you would want to do with copy only backups.
I get what you are trying to do and think I understand why...in your position, I would also worry about any databases in full recovery that don't have log backups. Or sizes of the logs just because the log backups aren't frequent enough. There are probably other things but thought I'd mention those.
Sue
August 7, 2018 at 3:46 pm
Thanks - I'd played with my original query and seemed to get what I wanted, thought I'd tried this last week and it didn't work:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc,max(bus.backup_size)/1024/1024/1024 AS BackupSize
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_desc
Yours looks better, but on 10.50.4302 returns no rows. Seems to work fine on the newer sql servers however.
Yes I'll be checking log backups too, I've been bitten by this before being used to Oracle automatically archiving its logs.
August 7, 2018 at 4:56 pm
Glen_A - Tuesday, August 7, 2018 3:46 PMThanks - I'd played with my original query and seemed to get what I wanted, thought I'd tried this last week and it didn't work:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc,max(bus.backup_size)/1024/1024/1024 AS BackupSize
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_descYours looks better, but on 10.50.4302 returns no rows. Seems to work fine on the newer sql servers however.
Yes I'll be checking log backups too, I've been bitten by this before being used to Oracle automatically archiving its logs.
The change in this one from the original one you posted is that you are querying for the max backup size (in addition to the max last backup time). So it returns the max size of any of the backups for that database. I returning the size for the last backup. So they return different things.
Sue
August 8, 2018 at 3:50 pm
Sue_H - Tuesday, August 7, 2018 4:56 PMGlen_A - Tuesday, August 7, 2018 3:46 PMThanks - I'd played with my original query and seemed to get what I wanted, thought I'd tried this last week and it didn't work:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc,max(bus.backup_size)/1024/1024/1024 AS BackupSize
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_descYours looks better, but on 10.50.4302 returns no rows. Seems to work fine on the newer sql servers however.
Yes I'll be checking log backups too, I've been bitten by this before being used to Oracle automatically archiving its logs.
The change in this one from the original one you posted is that you are querying for the max backup size (in addition to the max last backup time). So it returns the max size of any of the backups for that database. I returning the size for the last backup. So they return different things.
Sue
Thanks - trying to figure out why your query does not return any rows in version 10.50.x but works ok on 10.0.4x and it has me stumped so far..
August 8, 2018 at 4:07 pm
Glen_A - Wednesday, August 8, 2018 3:50 PMSue_H - Tuesday, August 7, 2018 4:56 PMGlen_A - Tuesday, August 7, 2018 3:46 PMThanks - I'd played with my original query and seemed to get what I wanted, thought I'd tried this last week and it didn't work:
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime, sdb.recovery_model_desc, sdb.state_desc,max(bus.backup_size)/1024/1024/1024 AS BackupSize
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.name not in ('master','model','msdb','tempdb')
GROUP BY sdb.Name, sdb.recovery_model_desc, sdb.state_descYours looks better, but on 10.50.4302 returns no rows. Seems to work fine on the newer sql servers however.
Yes I'll be checking log backups too, I've been bitten by this before being used to Oracle automatically archiving its logs.
The change in this one from the original one you posted is that you are querying for the max backup size (in addition to the max last backup time). So it returns the max size of any of the backups for that database. I returning the size for the last backup. So they return different things.
Sue
Thanks - trying to figure out why your query does not return any rows in version 10.50.x but works ok on 10.0.4x and it has me stumped so far..
No idea other than if it had no user databases as those are excluded.
Sue
August 8, 2018 at 4:19 pm
No, when I run my original query it returns 37 rows, when I also run this query to get database sizes it returns no rows:
SELECT sys.databases.name,
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY sys.databases.name
Seems that server has different permissions set up on how I connect using windows authentication, and appears to have SA acct disabled, so perhaps something to do with that?
August 8, 2018 at 4:42 pm
Glen_A - Wednesday, August 8, 2018 4:19 PMNo, when I run my original query it returns 37 rows, when I also run this query to get database sizes it returns no rows:
SELECT sys.databases.name,
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY sys.databases.nameSeems that server has different permissions set up on how I connect using windows authentication, and appears to have SA acct disabled, so perhaps something to do with that?
If you get some with your original ones and none with the one I wrote and there are user databases then it's more likely due to permissions for the system views/tables.
Check the permssions on all three of the views/tables between the different instances. And make sure to check the public role as well. The difference would likely be the permissions on sys.master_files.
Sue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply