For Full Backup
BACKUP DATABASE [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\RavisDatabase_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'RavisDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
split backup
BACKUP DATABASE
[Xnet_content_NAEU1] TO
DISK = N'L:\Bkup01\MSSQL11.SQLX\MSSQL\Backup\ManualBackups\Xnet_content_NAEU1_1.bak',
DISK = N'L:\ARCH01\Manual_Backup\Xnet_content_NAEU1_2.bak',
DISK = N'L:\ARCH01\Manual_Backup\Xnet_content_NAEU1_3.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'Xnet_content_NAEU1-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
For Differential Backup
BACKUP DATABASE [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\RavisDatabase_Diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'RavisDatabase-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
For Transactional Log Backup
BACKUP LOG [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\ RavisDatabase_Log.trn' WITH NOFORMAT, NOINIT, NAME = N'RavisDatabase-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
For Restore Database
RESTORE DATABASE [RavisDatabase] FROM DISK = N'C:\Ravis\Backup Files\RavisDataFull.bak' WITH FILE = 1, MOVE N'RavisDatabase_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_80.TEST\MSSQL\DATA\RavisDatabase.mdf', MOVE N'RavisDatabase_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_80.TEST\MSSQL\DATA\RavisDatabase_1.LDF', NOUNLOAD, STATS = 10
Restoration Database with Recovery
RESTORE DATABASE [RavisDatabase] WITH RECOVERY (for bring online database)
SP_HELP_REVLOGIN
Restore Database up to Specific Time only
RESTORE DATABASE [RavisDatabase] FROM DISK = [N'C:\Ravis\Backup Files\RavisDataFull.bak'] WITH NORECOVERY
GO
RESTORE LOG [RavisDatabase] FROM DISK = N'C:\Ravis\Backup Files\ RavisDatabase_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:\RavisData.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
BACKUP DATABASE [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\RavisDatabase_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'RavisDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
split backup
BACKUP DATABASE
[Xnet_content_NAEU1] TO
DISK = N'L:\Bkup01\MSSQL11.SQLX\MSSQL\Backup\ManualBackups\Xnet_content_NAEU1_1.bak',
DISK = N'L:\ARCH01\Manual_Backup\Xnet_content_NAEU1_2.bak',
DISK = N'L:\ARCH01\Manual_Backup\Xnet_content_NAEU1_3.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'Xnet_content_NAEU1-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
For Differential Backup
BACKUP DATABASE [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\RavisDatabase_Diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'RavisDatabase-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
For Transactional Log Backup
BACKUP LOG [RavisDatabase] TO DISK = N'C:\Ravis\Backup Files\ RavisDatabase_Log.trn' WITH NOFORMAT, NOINIT, NAME = N'RavisDatabase-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
For Restore Database
RESTORE DATABASE [RavisDatabase] FROM DISK = N'C:\Ravis\Backup Files\RavisDataFull.bak' WITH FILE = 1, MOVE N'RavisDatabase_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_80.TEST\MSSQL\DATA\RavisDatabase.mdf', MOVE N'RavisDatabase_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_80.TEST\MSSQL\DATA\RavisDatabase_1.LDF', NOUNLOAD, STATS = 10
Restoration Database with Recovery
RESTORE DATABASE [RavisDatabase] WITH RECOVERY (for bring online database)
SP_HELP_REVLOGIN
Restore Database up to Specific Time only
RESTORE DATABASE [RavisDatabase] FROM DISK = [N'C:\Ravis\Backup Files\RavisDataFull.bak'] WITH NORECOVERY
GO
RESTORE LOG [RavisDatabase] FROM DISK = N'C:\Ravis\Backup Files\ RavisDatabase_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:\RavisData.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
