Tag Archives: recovery model

Get information about your databases

  By | SQL Server w/o the GUI
Leave a comment

Let’s begin with the catalog view ‘sys.databases’. It has lots of useful information:

-- find the name, owner, create date and recovery mode of all user databases
SELECT 
   name,
   SUSER_SNAME(owner_sid),  -- SUSER_SNAME() returns the login when passed a 'sid'
   create_date, 
   recovery_model_desc
FROM 
   sys.databases
WHERE 
   database_id > 4;

You can also find the compatibility level, collation and state of the database, among other things. Database IDs 1 – 4 are the system databases:

1 = master
2 = tempdb
3 = model
4 = msdb

There’s a system stored procedure that gives similar information. Pass a database name as a parameter to find information about that database. If no argument is supplied, information is returned about all databases:

EXEC sp_helpdb N'AdventureWorks';
EXEC sp_helpdb;

Find the name, location and size of your database files using ‘sys.database_files’:

-- type_desc will let you know if it's the data or log file
SELECT 
   name, 
   type_desc, 
   physical_name, 
   size
FROM 
   sys.database_files