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

The error “A transport-level error has occurred when sending the request to the server”  can occur when SQL Server client cannot connect to the server. The reason for the lack of connection can become a wrong configured remote connections. 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.)

To solve this problem try to allow SQL Server to accept remote connections or check your firewall settings. It is possible to enable remote connections with the help of the SQL Server Configuration Manager tool.Remote connections

Invalid object name (Msg 208)

The “Invalid object name…” error appears when the name of the selected object is incorrect. For example, there is a need to selected “Column1” from the “Table_2” from the database “Adventureworks”, the following syntax was used:

SELECT Column1
FROM Tabl_2

In this case, SQL Server will send the next error:

Msg 208, Level 16, State 1, Line 4
Invalid object name 'Tabl_2'.

Let’s check the names of all tables in the database. It was found that there was a mistake, instead “Table_2” was “Tabl_2”. The following syntax should be applied to select “Column1” from “Table_2”:

SELECT Column1
FROM Table_2

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

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))

Then a decision was made to delete the primary key, the following command was executed:

ALTER TABLE Table_2
DROP PRIMARY KEY

The next error was received:

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'PRIMARY'.

It happened because the wrong syntax was used to delete the primary key. “DROP PRIMARY KEY” command is not suitable for SQL Server, it works with MySql. To delete primary key in SQL Server use the following syntax:

ALTER TABLE Table_2
DROP CONSTRAINT Table_2_Column1

Unclosed quotation mark after the character string (Msg 105)

The following error “Unclosed quotation mark after the character string…” occurs when you miss a quotation mark.

When the single quote is used once, and to close statement SQL Server is expecting for another one single quote. In some reason, a user can forget to do it. For example:

BACKUP DATABASE Adventureworks TO DISK = 'diff.bak

In this case, SQL Server will send the following error message:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'diff.bak'.  

Also, assume that the statement has been copied from MS Word document or website to SSMS and the “Unclosed quotation mark after the character string…” error appears. This happened because a single quote in MS Word or website ( ‘ ) is different from the single quote in SSMS ( ‘ ).

To fix this error just put another one single quote in the beginning or in the end of the statement where it needed:

BACKUP DATABASE Adventureworks TO DISK = 'diff.bak'

Incorrect syntax near (Msg 102)

The “Incorrect syntax near…” error appears if some symbol is missed. For example, the user decided to make a full database backup with the following command:

BACKUP DATABASE Adventureworks TO DISK 'full.bak'

Of course, an experienced user will immediately notice where a mistake was made. But if the error was not noticed the SQL Server will show the following message:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'full.bak'

SQL Server gives a hint that there is some symbol was missed before ‘full.bak’. Everything that is needed to do is to add “=”:

BACKUP DATABASE Adventureworks TO DISK = 'full.bak'

Database ‘Adventureworks ‘ already exists. (Msg 1801)

The “Database ‘Adventureworks ‘ already exists. Choose a different database name.” error appears if the database with the same name already exists. For example, a user decided to create new database backup with the following command:

CREATE DATABASE Adventureworks

If such database already existed the SQL Server will show the following message:

Msg 1802, Level 16, State 4, Line 4
Database 'Adventureworks ' already exists. Choose a different database name.

Everything that is needed to solve this problem is to choose another database name or delete the existing database.

CHECKSUM and VERIFYONLY

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 would 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.

CHECKSUM

The checksum is a value calculated from the data page bytes. Equal data pages have equal checksums. To make the checksum recorded into each page of the backup file the “WITH CHECKSUM” option needs to be added to “BACKUP DATABASE” command.

Using a CHECKSUM option makes database backup process longer, but if the database is really large it is better to use CHECKSUM option and be confident that all pages are written to the disk and ready for the restore process. To check the checksum value use the following SQL Server option:

VERIFYONLY

The VERIFYONLY  option does not restore the backup, it only verifies it. The VERIFYONLY performs several checks, such as:

  • Checksum
  • Availability of free space in destination place
  • Some header fields of database pages
  • Readable state of all volumes and the state of backup set

It is recommended to check the backup file before the restore process with the help of  VERIFYONLY option. If the file in proper state the SQLServer will display the following message:

The backup set on file 1 is valid.

If even a single byte is damaged the SQLServer will report the following:

Msg 3241, Level 16, State 0, Line 5
The media family on device 'd:\checksum.bak' is incorrectly formed. SQL Server cannot processthis media family.
Msg 3013, Level 16, State 1, Line 5
VERIFY DATABASE is terminating abnormally.

How to Use CHECKSUM And VERIFYONLY Options

Here are some ways of how to add CHECKSUM to the database backups and how to check them with the help of VERIFYONLY option:

T-SQL

Use T-SQL Command to work with CHECKSUM and VERIFYONLY options:

To make database backup simply add “WITH CHECKSUM” clause:

BACKUP DATABASE your_database TO DISK = 'backup_with_checksum.bak' WITH CHECKSUM

To check the database with VERIFYONLY option use the following syntax:

RESTORE VERIFYONLY FROM DISK = 'backup_with_checksum.bak'

SQL Server Management Studio

Another way of using CHECKSUM and VERIFYONLY options is to apply them in SQL Server Management Studio (SSMS):

For that you need to right click on the database you want to backup, select “Tasks”, then “Back up…”. In the window that opened select “Media Options” page and check “Verify backup when finished” and “Perform checksum before writing to media”. Look at the picture below:Checksum

SqlBackupAndFtp

If you prefer third-party utilities you can also use SqlBackupAndFtp to set up CHECKSUM and VERIFYONLY options:

Just scroll down to the “Backup options” section and check off “Enable CheckSum” and “Verify After Backup” options.

 

Backup chain

A backup chain is a specific sequence in which all database backups should be restored. If 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.

Example of Database Recovery

Many know that in order to minimize the risk of data loss it is advisable to use the full recovery model, which allows making transaction log backups. In their turn, transaction log backups enable database restoration to a particular point-in-time. Let’s consider the following sequence of database backups that form a database backup chain. It is assumed that the full database backup is made every 24 hours at 00:00, differential – every 6 hours, and transaction log backup is created every hour.  Backup sequence

Full Backup

Suppose some crucial data were deleted at 13:30. The best way out of this situation is to restore the database to the statement at 13:29. You should begin the restore process of this database from the last full database backup restore operation:
RESTORE DATABASE your_database FROM DISK = 'full_00_00.bak' WITH NORECOVERY, REPLACE

Differential Backup

After the full backup was restored the next necessary step would be restoring the last differential backup. If you are not sure which differential database backups belong to the restored full backup, you can first restore the headers of backups. Please use the following syntax to do it:
RESTORE HEADERONLY FROM DISK = 'full_00_00.bak'
RESTORE HEADERONLY FROM DISK = 'diff_06_00.bak' 
RESTORE HEADERONLY FROM DISK = 'diff_12_00.bak'  
The following result will be received:
Full&Diff
In this scenario you need to pay attention to the full backup CheckpointLSN value and compare it with Differential backups DatabaseBackupLSN value – they should be identical (marked as blue square). It means that these two differential backups belong to this full backup and can be restored after it. To find out what differential backup was made earlier, compare their FirstLSN numbers (marked as orange square). The last differential backup will have bigger value.
If you try to restore a differential backup before restoring its full backup, you will get the following error message:
Msg 3136, Level 16, State 3, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

That means that the backup chain was broken and any further recovery process is not possible. Therefore, pay attention to what differential backup should be restored after the full backup. In our example it is necessary to restore a differential database backup from 12:00 using the following command:

RESTORE DATABASE your_database FROM DISK = 'diff_12_00.bak' WITH NORECOVERY

Transaction Log Backups

Now that we’ve restored the full and differential backups, let’s restore the database to its state as of 14:29. This is where we will require the transaction log backups. In this case it is also necessary to follow a certain sequence to avoid breaking transaction log backup chain.
If you need to determine what transaction log backup follows after another, you need to get backup’s headers (same as we did with differential backups):
T.Log sequence
This picture shows a transaction log backup chain, each backup from the chain has its own FirstLSN and LastLSN. Every LastLSN value of the transaction log must be the same as FirstLSN values of the next transaction log backup. These values help to restore transaction log backup files in the right sequence. To find out what transaction log backup should be restored after the differential backup, compare their DatabaseBackupLSN values – they should be identical:
T.Log-and-Diff
To understand which transaction log backup should be restored first after differential backup, please check LastLSN value of the differential backup. This value should be somewhere between the FirstLSN and the LastLSN values of the transaction log backup. Otherwise, if the wrong transaction log backup is applied to the differential backup, you’ll get the following error:
Msg 4305, Level 16, State 1, Line 17
The log in this backup set begins at LSN 74000000071200001, which is too recent to apply to the database. An earlier log backup that includes LSN 74000000065600001 can be restored.
Msg 3013, Level 16, State 1, Line 17
RESTORE LOG is terminating abnormally.
To restore transaction log use the following syntax:
RESTORE LOG your_database FROM DISK = 'log_13_00.bak' WITH NORECOVERY 
Then to restore the database up to the state at 13:29:59 run the foloowing command for transaction log from 14:00 using “WITH STOPAT” option:
RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH STOPAT = '2015-11-19 13:29:59.000', RECOVERY

Partial Backup

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.

Let’s consider the picture below to understand how partial backup and differential partial backup work:

Partial Backup

Below I’ll explain what is shown on the picture. Take a huge database that uses simple recovery model, where the full database backup was made at 12:00:

BACKUP DATABASE your_database TO DISK = 'full.bak'

Because the size of the database is large and it takes more time for making backup, a decision was made to create partial database backup, which includes the primary filegroup and read-write secondary filegroup:

BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_full.bak'

This backup takes less time than full database backup. Following that some changes were made in the primary filegroup and the read-write secondary filegroup. These changes were stored in the following differential partial backup that was created at 14:00:

BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_diff.bak' WITH DIFFERENTIAL

Note that SQL Server Management Studio does not support partial backups.

Here is another, more interesting case. Assume that the database was changed from read-only to read-write after partial backup was made. In this case, perhaps, some secondary read-write filegroups will not be included in the partial backup, therefore, when the time comes to make a differential partial backup the following error occurs:

Msg 233, Level 20, State 0, Line 2
A transport-level error has occurred when sending the request to the server.

To avoid this error, make another partial backup before a differential partial backup. Following this operation, the new partial backup will contain every read-write secondary filegroup.

Restore Process

The commands for the database restore process from the example above should be executed in the following order:

RESTORE DATABASE your_database FROM DISK = 'full.bak', WITH NORECOVERY 
GO 
RESTORE DATABASE your_dataabse FROM DISK = 'partial_backup_full.bak', WITH NORECOVERY  
GO
RESTORE DATABASE your_database FROM DISK = 'partial_backup_diff.bak', WITH RECOVERY  
GO

Log Sequence Number

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 occurred 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' 

To understand what these LSNs mean in each database backup let’s consider a backup sequence created (the database uses full or bulk-logged recovery model) using the following commands:

BACKUP DATABASE your_database TO DISK = 'full.bak'
BACKUP LOG your_database TO DISK = 'log.bak'
BACKUP LOG your_database TO DISK = 'log1.bak'
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
BACKUP LOG your_database TO DISK = 'log2.bak'
BACKUP LOG your_database TO DISK = 'log3.bak'
BACKUP DATABASE your_database TO DISK = 'diff1.bak' WITH DIFFERENTIAL
BACKUP DATABASE your_database TO DISK = 'full1.bak'
BACKUP LOG your_database TO DISK = 'log4.bak'

This shows that we have created three different types of database backups: Full backup, Differential backup and Transaction Log backup. Now let’s check their LSNs and compare them:

RESTORE HEADERONLY FROM DISK = 'full.bak' 
RESTORE HEADERONLY FROM DISK = 'log.bak' 
RESTORE HEADERONLY FROM DISK = 'log1.bak' 
RESTORE HEADERONLY FROM DISK = 'diff.bak' 
RESTORE HEADERONLY FROM DISK = 'log2.bak' 
RESTORE HEADERONLY FROM DISK = 'log3.bak'
RESTORE HEADERONLY FROM DISK = 'diff1.bak' 
RESTORE HEADERONLY FROM DISK = 'full1.bak'
RESTORE HEADERONLY FROM DISK = 'log4.bak' 

These commands produce the following results:
LogSequenceNumber

where:

  • FirstLSN – Log sequence number of the first transaction in the backup set
  • LastLSN – Log sequence number of the next log record after the backup set
  • CheckpointLSN – Log sequence number of the last checkpoint
  • DatabaseBackupLSN – Log sequence number of the last full database backup

How do FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN Relate to Each Other

The DatabaseBackupLSN of the first full backup is always equal to zero:Full(DatabaseBackupLSN)

The FirstLSN of the first full backup is the same as the CheckpointLSNFull FirstLSN and CheckpointLSN

Full database backup is the main type on which all other backups (especially differential backups) are dependent. In the restore sequence a differential backup is always restored after the full backup. In order to determine the full backup to which differential backup relates, you need to check the values of DatabaseBackupLSN of differential backup and CheckpointLSN of the full backup – their values should be identical.: Full and Differential LSN

Full CheckpointLSN and Differential DatabaseBackupLSN

But what if there is no differential backup, and there is a need to restore the database with the full and transaction log backups? To find out which transaction log backup should be restored right after the full backup is restored take a look at the LastLSN of the full backup and the FirstLSN and the LastLSN of the transaction log backup. The LastLSN of the full backup should be between the FirstLSN and the LastLSN of the transaction log backup. Full and Tran.Log

Likewise, in order to find out which transaction log backup should be restored after the differential backup you need to pay attention to the LastLSN of the differential backup and the FirstLSN and the LastLSN of the transaction log backup. The LastLSN of the differential backup should be between FirstLSN and LastLSN of the transaction log backup followed right after it: Differential LSN and Tran.Log LSNThe CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup:

Differential CheckpointLSN and Transaction Log CheckpointLSN

First transaction log backup has the same FirstLSN and CheckpointLSN as the first full database backup’s CheckpointLSN:Full CheckpointLSN and Transaction Log FirstLSN and CheckpointLSN
For a transaction log backup, the LastLSN value is the same as the FirstLSN of the next transaction log backup:Transaction Log LastLSN and Transaction Log FirstLSN
Transaction Log LSN

Note that LastLSN value of the previous transaction log backup is equal to LSN of the first log record stored in the following transaction log backup, as shown on the picture above.