Basics of SQL Server 2008 R2 for DBA

List of all database with recovery modes and version information.

'--- SQL 2005

SELECT NAME,COMPATIBILITY_LEVEL,RECOVERY_MODEL_DESC,STATE_DESC  FROM SYS.DATABASES

--- SQL 2000

SELECT NAME,CMPTLEVEL,DATABASEPROPERTYEX(NAME,'RECOVERY')AS RECOVERYMODEL,

DATABASEPROPERTYEX(NAME,'STATUS') AS STATUS FROM SYSDATABASES

------------------------------------------------------------------------------------------------------------------------------------------

List database Names with Physical location where the data files reside.

--- SQL 2005
SELECT DB_NAME(DATABASE_ID) AS DATABASENAME,NAME,TYPE_DESC,PHYSICAL_NAME FROM SYS.MASTER_FILES
--- SQL 2000
SELECT DB_NAME(DBID) AS DATABASENAME,NAME,FILENAME FROM MASTER.DBO.SYSALTFILES

e='m 2 n b ��m H p ;margin-bottom:.0001pt;line-height: normal'>How to retrieve the configuration paramaters in SQL Server 2005?

'SELECT * from sys.configurations order by NAME

or

SP_CONFIGURE 'show advanced options',1

go

RECONFIGURE with OVERRIDE

go

SP_CONFIGURE

Go

List of all database and its last backup taken on SQL Server .

SELECT db.name,case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100),

 MAX(b.backup_finish_date)) end AS last_backup_finish_date

FROM sys.databases db

LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'

 WHERE db.database_id NOT IN (2) GROUP BY db.name

ORDER BY 2 DESC