Sql Server Error Message : Msg 3098, Level 16, State 2, Line 10 The backup cannot be performed because ‘COMPRESSION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘COMPRESSION’ or specify ‘NO_COMPRESSION’. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

Posted by Sqltimes
On November 29, 2014
0 Comments

Earlier a few weeks ago, I ran into an interesting error message. Msg 3098, Level 16, State 2, Line 10 The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a […]

Posted by Sqltimes
On November 26, 2014
0 Comments

Quick one today: Common Table Expression, since it was introduced in Sql Server 2005 is another great feature. The value is immense and cannot be understated. It reduced the need for temporary tables and table variables in scripts a lot. Make code more intuitive All the tables in multiple CTE’s are touched only when the […]

Posted by Sqltimes
On November 22, 2014
0 Comments

Quick one today: On a table, we could create Clustered and NonClustered indexes. Each table can only have 1 clustered index, but multiple nonclustered index. When a primary key is created on a column, it by default becomes Clustered index. But sometimes, we run into a situation where we need to separate PRIMARY KEY and […]

Posted by Sqltimes
On November 15, 2014
0 Comments

Quick one today: Rare, but sometimes, there is a need to INSERT specific values into a table that has an IDENTITY specified on one of the columns. Luckily, Sql Server provides mechanisms to insert data in such situations. When we try to insert values into an IDENTITY column, we get errors like this. Msg 544, […]

Posted by Sqltimes
On November 8, 2014
0 Comments

Quick one today: Earlier last month, while running server side traces on a lab Sql Server machine, I ran into this interesting error. Error Details: Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(Access is denied.). Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1 Could not create a trace file. This is easy […]

Sql Server: Common Table Expression (CTE) with ORDER BY clause

Sql Server: How to create PRIMARY KEY without CLUSTERED Index

Sql Server INSERT data into an IDENTITY column with SET IDENTITY_INSERT ON

Sql Server : Error : Could not create a trace file (SP_TRACE_CREATE)