You may encounter problems when attempting to restore a Litespeed backup on a database in single user mode within SSMS.
Sample script shown below:
ALTER DATABASE Database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Exec Master.dbo.XP_RESTORE_BACKUP
@database = NDatabase_name,
@filename = NL:\backup\Database_name.bak,
@filenumber = 1,
@with = NREPLACE,
@with = NSTATS = 10,
@with = NMOVE NData_1 TO NC:\datafiles\data\data1.mdf,
@with = NMOVE NData_2 TO ND:\datafiles\data\data2.ndf,
@with = NMOVE Nlog TO NE:\datafiles\log\log1.ldf,
@affinity = 0,
@logging = 0
Alter database database_name SET MULTI_USER
GO
The above script will most certainly fail because XP_RESTORE_BACKUP will always try to open a second connection.
You do not want to use the command prompt nor kill cursor technique.
This is possible with the use of Native Backup e.g.
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [Test] FROM DISK = NE:\Backups\test.bk
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
ALTER DATABASE Test SET MULTI_USER
GO
If you want to achieve this with the use of Litespeed Backup, all you need to do is to install LiteSpeed Engine For SQL Server and add the extensions to filter using that UI (See attachment). You can use any NORMAL SQL restore script as the Litespeed engine for SQL sever product will kick in based on the file extension of the backup file. Litespeed Engine for SQL server will be activated whenever a SQL script or job accesses or creates a backup file with the extension that you configured the product to look for.
e.g. If you perform a backup using Litespeed with file nameNE:\Backups\test.bak, you should be able to restore this Litespeed backup file with the script below;
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [Test] FROM DISK = NE:\Backups\test.BAK
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
ALTER DATABASE Test SET MULTI_USER
GO
Litespeed engine kicks in as soon as the process identifies the test.bak file.
