Some Useful DBA Commands

For Full Backup

BACKUP DATABASE [AdventureWorks2008R2] TO  DISK = N'C:\Gaurav\Backup Files\AdventureWorks2008R2_Full.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008R2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

For Differential Backup

BACKUP DATABASE [AdventureWorks2008R2] TO  DISK = N'C:\Gaurav\Backup Files\AdventureWorks2008R2_Diff.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008R2-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

For Transactional Log Backup

BACKUP LOG [AdventureWorks2008R2] TO  DISK = N'C:\Gaurav\Backup Files\ AdventureWorks2008R2_Log.trn' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008R2-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

For Restore Database

RESTORE DATABASE [AdventureWorks2008R2] FROM  DISK = N'C:\Gaurav\Backup Files\AdventureWorksFull.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2008R2_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\AdventureWorks2008R2.mdf',  MOVE N'AdventureWorks2008R2_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\AdventureWorks2008R2_1.LDF',  NOUNLOAD,  STATS = 10

Restoration Database with Recovery

RESTORE DATABASE [AdventureWorks2008R2] WITH RECOVERY (for bring online database)

SP_HELP_REVLOGIN

Restore Database up to Specific Time only

RESTORE DATABASE [AdventureWorks2008R2] FROM  DISK = [N'C:\Gaurav\Backup Files\AdventureWorksFull.bak'] WITH NORECOVERY

GO

RESTORE LOG [AdventureWorks2008R2] FROM  DISK = N'C:\Gaurav\Backup Files\ AdventureWorks2008R2_Log.trn ' WITH RECOVERY,

STOPAT= ‘MARCH 23, 2009 05:31:00 PM’

GO

Checking to make sure a SQL Server backup is useable

               RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK

        GO

For used space on the particular database

SP_SPACEUSED

Check the update statistics if SQL Server

SP_UPDATESTATS

Display the Configuration Setting of the Current Server

SP_CONFIGURE

Check the Log Space of All Databases of Server

DBCC SQLPERF(LOGSPACE)

Check for the Drive Size of the Physical Server

XP_FIXEDDRIVES

For Check the Error Log

SP_READERRORLOG

Data File and Log File Location for All Server

SELECT DB_NAME (DATABASE_ID), NAME, TYPE_DESC, PHYSICAL_NAME, [SIZE(MB)] = (SIZE * 8) / 1024.0 FROM  SYS.MASTER_FILES

For Missing Index Details

SELECT *FROM SYS.DM_DB_MISSING_INDEX_DETAILS (MISSING INDEX)

Check Recovery Model

SELECT NAME, RECOVERY_MODEL_DESC FROM SYS.DATABASES

WHERE NAME = ['DB_NAME']

Getting All Table Name from particular database

USE [DB_NAME]

GO

SELECT * FROM SYS.TABLES

GO

Number of Cores on the Server

SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs

, CASE

WHEN hyperthread_ratio = cpu_count THEN cpu_count

ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )

END AS NumberOfCoresInEachCPU

, CASE

WHEN hyperthread_ratio = cpu_count THEN cpu_count

ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )

END AS TotalNumberOfCores

, cpu_count AS NumberOfLogicalCPUs

, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB

FROM

sys.dm_os_sys_info

Check Dead Lock on Server

SELECT  L.request_session_id AS SPID,

DB_NAME(L.resource_database_id) AS DatabaseName,

O.Name AS LockedObjectName,

P.object_id AS LockedObjectId,

L.resource_type AS LockedResource,

L.request_mode AS LockType,

ST.text AS SqlStatementText,

ES.login_name AS LoginName,

ES.host_name AS HostName,

TST.is_user_transaction as IsUserTransaction,

AT.name as TransactionName,

CN.auth_scheme as AuthenticationMethod

FROM    sys.dm_tran_locks L

JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id

JOIN sys.objects O ON O.object_id = P.object_id

JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id

JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id

JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id

JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id

CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

WHERE   resource_database_id = db_id()

ORDER BY L.request_session_id

Checking Locking Process

EXEC SP_LOCK

EXEC SP_WHO2

USE [DB_NAME]

GO

SELECT * FROM SYS.DM_TRAN_LOCKS

GO

Bring Database Offline

ALTER DATABASE [DB_Name] SET OFFLINE WITH
ROLLBACK IMMEDIATE

Bring Database Online

ALTER DATABASE [DB_Name] SET ONLINE

Check which Backup Failed on when

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current

EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)

EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)

Most Recent Database Backup for Each Database 

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name  
ORDER BY  
   msdb.dbo.backupset.database_name

Database Backups for all databases For Previous Week 

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date

Check Database ISOLATION Level

DBCC USEROPTIONS

Buffer Cache Hit Ratio

SELECT object_name, counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Buffer Manager%'

AND [counter_name] = 'Buffer cache hit ratio'

Who is disable the job on sql server

EXEC msdb.dbo.sp_helpjob

Physical Location for the database files (data file and log file)

SELECT NAME, PHYSICAL_NAME AS CURRENT_FILE_LOCATION FROM SYS.MASTER_FILES

Checking the Log shipping Last Backup LSN Number for backups of primary and secondary server.

SELECT SECONDARY_DATABASE, LAST_COPIED_FILE, LAST_RESTORED_FILE FROM MSDB..LOG_SHIPPING_MONITOR_SECONDARY

Find SQL Server Startup Time.

SELECT SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO

 

Find SQL Server Database Size.

USE DATABASE [DATABASE_NAME]

GO;

SELECT

                        DBNAME,

                        NAME,

                        [FILENAME],

                        SIZE AS 'SIZE(MB)',

                        USEDSPACE AS 'USEDSPACE(MB)',

                        (SIZE - USEDSPACE) AS 'AVAILABLEFREESPACE(MB)'

FROM            

(          

SELECT

DB_NAME(S.DATABASE_ID) AS DBNAME,

S.NAME AS [NAME],

S.PHYSICAL_NAME AS [FILENAME],

(S.SIZE * CONVERT(FLOAT,8))/1024 AS [SIZE],

(CAST(CASE S.TYPE WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(S.NAME, 'SPACEUSED') AS FLOAT)* CONVERT(FLOAT,8) END AS FLOAT))/1024 AS [USEDSPACE],

S.FILE_ID AS [ID]

FROM

SYS.FILEGROUPS AS G

INNER JOIN SYS.MASTER_FILES AS S ON ((S.TYPE = 2 OR S.TYPE = 0) AND S.DATABASE_ID = DB_ID() AND (S.DROP_LSN IS NULL)) AND (S.DATA_SPACE_ID=G.DATA_SPACE_ID)

) DBFILESIZEINFO