Earlier I wrote blog article SQL SERVER – Finding Last Backup Time for All Database. In the response of this article I have received very interesting script from SQL Server Expert Matteo as a comment in the blog. He has written script using sys.databases which provides plenty of the information about database.
I suggest you can run this on your database and know unknown of your databases as well.
SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
-- last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
Please let me know if you find this information useful.
Watch a 60 second video on this subject
[youtube=http://www.youtube.com/watch?v=8P5TuOg3PlA]
Reference: Pinal Dave (https://blog.sqlauthority.com)
40 Comments. Leave new
Hi sir,
This is really helpful , those who want to know their database information.
Thanks
very helpful script, indeed.
useful as usual..
I have a question, how can I change compatibility level of one database? for example improve it from 2005 to 2008?
tnx
exec sp_dbcmptlevel ‘db_name’,’100′
Actually, what would be essential also is the database file locations: for a database – is the log, the data files and FT catalogs on the same disk? And for all databases as a group – are they on the same disk? Are they on the System Disk?
…and now with the Denali CTP out, perhaps it’s time to add another line to the compatibility CASE statement, eh?
Excellent script…will be adding it to my library immediately. Thanks Pinal.
Very Good script. Thanks Pinal
Good Script Pinal. Do you something similar script that gathers data from multiple sql servers. Thanks Syed
Then you just can use CMS (Central Management Servers). Look at this or this for a who too do it. I use every day for getting info about my sql servers. Both Test and Prod.
Hi Pinal when i am executing the backup script it thorws me into error at ‘order’ .can you suggest me .
Can this be modified to count the rows of each database?
Did you mean the count of each tables in a database?
thankyou
Thank you, this is really helpful.
Can I get information about the database creator and the host name of this database user?
Very nice script….Thank you Pinal
i want to get details of pc-name or ip who is connected now with specific database, and also i need query to kill process by using query
EXEC sp_who
I ran the script on one of my SQL Server 2005 servers, and some of the creation dates were displayed in DD-MM-YYYY format and some in MM-DD-YYYY format. When I queried sys.databases, the create-date column values were all displayed in YYYY-MM-DD format. Can anyone explain this, and how to display all of the dates in MM-DD-YYYY format?
I am getting the error message as below:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.master_files’.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.master_files’.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.master_files’.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.master_files’.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.databases’.
Please guide me to get this rectified
i still dont understand.
\
This is not working in SQL 2000.. please guide me on this…
Very, very informative & helpful script. Thank you, Pinal!
I’m working on this now but is there a way to use this script to pull only info for a specific database? I’d like to implement this into a database admin tool I am writing.
Thanks again for all the help you provide.
Useful script. Database back up location should have been a useful inclusion. Thanks.
Hi Sir,
Im given a task of finding the SQL version of 400 + servers. Im frustrated. Is there any script to do so. Please just accordingly