Friday, 5 April 2013

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

2 comments:

I thought you'd be interested in this job at NICE or know someone who might be a good match.

Job: Tech Lead SQL Developer  I thought you'd be interested in this job at NICE or know someone who might be a good match.