String comparision

There are various method through which we can compare string.


SELECT AccountName FROM AccountMaster WHERE AccountName='REKHA'

SELECT AccountName from AccountMaster WHERE AccountName LIKE '%REKHA%'

SELECT AccountName from AccountMaster WHERE AccountName LIKE '%_REKHA_%'

SELECT AccountName from AccountMaster WHERE AccountName LIKE '%[OO]_%'
SELECT * FROM AccountMaster

INSERT INTO AccountMaster SELECT('SMANJAYQAZXSW')
SELECT * FROM AccountMaster
SELECT * FROM AccountMaster WHERE AccountName IN('SANJAY','REKHA', 'NIKITA')

SELECT AccountName FROM AccountMaster WHERE AccountName LIKE 'S%[NJ]%_Y'

This query searches BETWEEN Start TO End withing a string.

SELECT AccountName FROM AccountMaster WHERE AccountName LIKE 'S____Y'


This one will validate the format of a string.  As shown it will return "Valid".  Change the "8" to an "A" and you get "Invalid":
Declare @string VarChar(50) = '741ZVM543'
SELECT CASE WHEN @string LIKE '[0-9][0-9][A-Z][A-Z][0-9][0-9][0-9]' THEN 'Valid' ELSE 'Invalid' END


This one will find the first upper-case character in a string (it returns "7"):
Declare @string VarChar(50) = 'white Chem'
SELECT PatIndex('%[A-Z]%',@string collate Latin1_General_BIN)

Finding the details of Backup File without restoring it

Sometime we may have to find details of a SQL backup file without restoring it. Some of the common requirements are:

1. When the backup Started and Finished.
2. Is the backup file is good.
3. Who had taken this backup.
4.  What is the type of backup.
5. Is backup Compressed
6. Database Creation Date
7. SQL Server Version
8. From which system backup was taken
9. Database Collation
10. Recovery Model

We can get all the information wsing below query:

RESTORE HEADERONLY FROM DISK = N'C:\Temp\Data.bak'

Output: 

Column name	Description for SQL Server backup sets
BackupName	Backup set name.
BackupDescription	Backup set description.
BackupType	Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = Partial 8 = Differential partial
ExpirationDate	Expiration date for the backup set.
Compressed	Whether the backup set is compressed using software-based compression: 0 = No 1 = Yes
UserName	User name that performed the backup operation.
ServerName	Name of the server that wrote the backup set.
DatabaseName	Name of the database that was backed up.
DatabaseVersion	Version of the database from which the backup was created.
BackupSize	Size of the backup, in bytes.
DatabaseCreationDate	Date and time the database was created.
BackupStartDate	Date and time that the backup operation began.
BackupFinishDate	Date and time that the backup operation finished.
CompatibilityLevel	Compatibility level setting of the database from which the backup was created.
IsSnapshot	1 = Snapshot backup.
HasBackupChecksums	1 = Backup contains backup checksums.
IsReadOnly	1 = Database was read-only when backed up.
IsSingleUser	1 = Database was single-user when backed up.
IsDamaged	1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
HasBackupChecksums	1 = Backup contains backup checksums.
IsForceOffline	1 = Backup taken with NORECOVERY; the database was taken offline by backup.
IsCopyOnly	1 = A copy-only backup. A copy-only backup does not impact the overall backup and restore procedures for the database
RecoveryModel	Recovery model for the Database, one of: FULL BULK-LOGGED SIMPLE

Get all information for all Database

Use below query to find all information for all database.


SELECT Database_ID, CONVERT(VARCHAR(25), DB.Name) AS DBName, 
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status], State_Desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data File Size (MB)],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log File Size (MB)],
User_Access_Desc AS [User Access], Recovery_Model_Desc AS [Recovery Model], 
CASE Compatibility_Level 
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
WHEN 110 THEN '110 (SQL Server DENALI)'
END AS [DB Compatibility Level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation Date],
ISNULL((SELECT TOP 1 CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last Backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [FullText],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [AutoClose],
page_verify_option_desc AS [Page Verify Option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [Read Only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [AutoShrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [Auto Create Statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [Auto Update Statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [StandBy],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [Cleanly Shutdown]
FROM sys.databases DB ORDER BY DBName, [Last Backup] DESC, NAME


Ref: http://blog.sqlauthority.com

TempDB space needed to execute checkdb.

Sometime we may need to check the space required to execute CheckDB. We can run this query in live database because actually it is not running CheckDB Query. It is only checking the amount of space required to run this query. Use Below query to check this:

DBCC CheckDB('Database_Name') WITH EstimateOnly

Example:

DBCC CheckDB('DemoDatabase') WITH EstimateOnly

It will the below output:

Database Mirroring login attempt by User ' ' failed with error: 'Connection handshake failed',

Sometime you may receive an error in SQL Server's log and Database Mirroring stops:

Database Mirroring login attempt by user ' ' failed with error: 'Connection handshake failed. The login ' ' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: ]

This message actually informs that your security configuration for the mirroring protocol is broken. You can easily rectify this error by fixing the permission on endpoint using below SQL:

GRANT Connect on EndPoint::[EndPoint Name] To [Account]

Below command will fix this issue assuming end point name is “mirroring” and account name used is “ArunL\test”

GRANT Connect on EndPoint::[EndPoint Name] To [ArunL\test]

Note: If your mirroring is suspended then you have to resume mirrirong after this command.