Author: Alexandr Omelchenko

A transport-level error has occurred when sending the request to the server (Msg 233)

Alexandr Omelchenko

The error “A transport-level error has occurred when sending the request to the server”  can occur when the SQL Server client cannot connect to the server. The reason for the lack of connection can become a wrong-configured remote connection. In this case, SQL Server will send the following message:

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Read More

Incorrect syntax near the keyword ‘PRIMARY’ (Msg 156)

Alexandr Omelchenko

The error “Incorrect syntax near the keyword ‘PRIMARY’” can occur if the user tries to execute the command with a wrong syntax. Let’s consider the following example:

Assume there is a database “Adventureworks” in which new table “Table_2” was created. Two columns “Column1” and “Column2” were added to the “Table_2”, “Column1” was selected as Primary Key. Please pay attention to the following syntax:

CREATE TABLE Table_2
(Column1 INT NOT NULL,
Column2 VARCHAR(300)
CONSTRAINT Table_2_Coumnl1 PRIMARY KEY CLUSTERED (Column1))

Read More

CHECKSUM and VERIFYONLY

Alexandr Omelchenko

A database backup process is an integral part of a recovery system. All backups must be operable so that they can be restored at any time. It is recommended to make regular database backups and the majority of SQL Server users actually follow this recommendation. But how can you be sure that you will be able to restore the database from the backups? Sometimes backups can be corrupted. This is why it is useful to test database backups for their integrity. Of course, the best check for any backup is a restore to a server, but there are some other, less radical ways of doing it. Let’s explore CHECKSUM and VERIFYONLY options. Read More

Backup chain

Alexandr Omelchenko
A backup chain is a specific sequence in which all database backups should be restored. If a backup chain is broken, the restore process will be not possible, and some data will be lost. Let us consider the following example with all three backup types to understand the backup chain better.

Read More

Partial Backup

Alexandr Omelchenko

The peculiarity of partial backup is that it does not contain read-only filegroups. So it’s a good choice if your database has a read-only filegroup and there is no need to backup it all of the time. Though all recovery models in SQL Server support partial backup, it was primarily designed for use under the simple recovery model as it makes backup of huge databases, containing one or more read-only filegroups, more flexible.

A partial backup can be applied both to full or differential backups, though it is not applicable to transaction log backup. As far as differential partial backup is concerned, only the data extents that have changed in the filegroups since the previous partial backup are recorded into it. Please note that differential partial backups can be applied only with partial backups. Read More

Log Sequence Number

Alexandr Omelchenko

Every record in SQL Server transaction log has its own log sequence number or LSN. Log sequence numbers are ordered in such a way that changes related to the record with greater LSN occur after the changes related to records with smaller LSN. Each backup that is set in SQL Server has FirstLSN and LastLSN values. To find out all backup’s LSN values you need to look at its header using the following syntax:

RESTORE HEADERONLY FROM DISK = 'full.bak' 

Read More