SQLCMD Mode in Query Editor (!! for SQL CMD) Execute Operating System Command from SQL Server

By enabling the SQL CMD mode we can execute SQLCMD Scripts from the management studio along with T-SQL Statements.

We need to enable SQLCMD Mode to write sqlcmd scripts
SSMS -- Query Menu -- SQLCMD

After enabling the SQLCMD we can execute queries by using !!

select * from kalyandb..Items
!!dir
select count(*) from kalyandb..Images

We can execute complete set from the Query Editor. Here we should remember one thing, if you enable SQLCMD, intellisense will not work.

Tail Log Backups in SQL Server

What is Tail-Log Backup?

A tail-log backup captures the log records that have not been backed up since the recent last log backup to current point in time before restore. Generally we generate a tail log to make database into NoRecovery mode (Inaccessible). The tail log backup can be generated from FULL / BULK-LOGGED Recovery model databases.

Unlike other log backups, this tail log backup can be generated even if database is damaged. The tail log backup is also generated using backup log command only with two options a) NoRecovery and b) continue_after_error

NoRecovery – We need to use this option when database is online, After issuing this command it ensures Database is not changed after tail-log backup, it makes database in restoring state, if database is in restoring state, users requests will not processed.

Backup log database_name to disk =’path of tlog file’ with norecovery

No_Truncate / Continue_after_error – Using one of this options we can generate a tail-log backup on a damaged database.

a) Create a database and table in it
b) Insert few records into the table
c) Offline the database and rename data file
d) Try to bring the database online, it wont allow because, it wont find the data file

At that time we can use the below command to generate tail log file from a damaged database.

Backup log damaged_dbname to disk=’path of tlog file’ with no_truncate

Using No_Truncate option, the current active transaction log can be backedup when the data files are inaccessible or damaged or missing and log file is undamaged.

Backup log damaged_dbname to disk=’path of tlog file’ with continue_after_error

Full Text Index in SQL Server

If we have varbinary(max), or varchar(max) columns in the sql server table we cannot create indexes on those columns using the standard clustered or non-clustered indexes. The Full Text Index is the solution for this. Full Text Index can be achieved through full text search feature in SQL Server. It helps to run the full-text queries against character based data in sql server tables.


Steps

1. Create a full text catalog

Use DatabaseName
Create FullText Catalog MyFTCatalog
Select fulltext_catalog_id, name from sys.fulltext_catalogs

After creating full text catalog we need to create full text index on the required table. To create full text index, Table must be having unique index. If you don’t specify the catalog name while creating full text index then it will attach to default catalog.

2. Create full Text Index

Create FullText Index on Items(ItemDescription, ItemContent Type Column ItemExtention Language 1033)
Key Index Ix_Item_ID on MyFTCatalog

Select * from sys.fulltext_languages – Full Text Supported Languages.
Select * from sys.fulltext_document_types – Displays list of document types supported by full text search.

Below Query is to display list of catalog names and table name in the catalog

SELECT st.name AS TableName, sc.name AS FTCatalogName
FROM sys.tables st, sys.fulltext_indexes si, sys.fulltext_catalogs sc
where st.object_id = si.object_id and si.fulltext_catalog_id = sc.fulltext_catalog_id

Select * from Items
Where contains([ItemContent], '"*Microsoft*" OR "*SQL Server*"');

Select * from Items
Where FreeText([ItemContent], '"*Microsoft*" OR "*SQL Server*"');