Wednesday, December 29, 2010

SQL Server LiteSpeed Restore DB and Familier Errors




Search Engine OptimizationSubmit Express
SQL Server LiteSpeed Restore DB and Familier Errors

Who should read this blog: MS SQL DBA to get familier with Litespeed Restore process and understand important litespeed errors.

First I shall try to treat lite speed backup file as I native backup. This throws Sql server error 3241.

RESTORE DATABASE LiteSpeedDB
FROM DISK = 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

Msg 3241, Level 16, State 0, Line 1
The media family on device 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Let's Understand: Install litespeed and try to restore the DB using extended stored procedure xp_restore_database. Following statement throws error. Go through the error and resolution as follows:

EXEC master..xp_restore_database
@database='LiteSpeedDB',
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

LiteSpeed(R) for SQL Server Version 6.1.1.1011
Copyright 2010 Quest Software, Inc.

Msg 61700, Level 16, State 1, Line 0
Unexpected termination on thread: 0, Return code: x80770004

RESTORE DATABASE is terminating abnormally.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
File 'LiteSpeedDB_Log' cannot be restored to 'o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
File 'LiteSpeedDB_Index' cannot be restored to 'h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf" failed with the operating system error 3(The system cannot find the path specified.).
File 'LiteSpeedDB' cannot be restored to 'h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf" failed with the operating system error 3(The system cannot find the path specified.).

Let's Understand:  Sql server error 61700 and operating system error 3 indicates that the files of this DB reside on the drives which doesn't exist on this server. eg. The files of the DB resides in H and I drive but your system doesn't have these files.

Custom Search
EXEC master..xp_restore_filelistonly
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

Let's verify the backup file:

EXEC master..xp_restore_verifyonly
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

LiteSpeed(R) for SQL Server Version 6.1.1.1011
Copyright 2010 Quest Software, Inc.

The backup set on file 1 is valid.
Directory lookup for the file "o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf" failed with the operating system error 3(The system cannot find the path specified.).
The path specified by "h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf" is not in a valid directory.
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

CPU Seconds: 0.28
Environment: Intel(R) Pentium(R) M processor 1.86GHz
CPUs: 1 logical, 1 core, 1 physical package.

The backup file is valid.

Let's Understand The above information indicates that the backup file is valid. Directory lookup fails with "operating system error 3" because there are three files in the original DB but the directory for these files are missing on the server.

EXEC master.dbo.xp_restore_database @database = 'LiteSpeedDB'
, @filename = 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'
, @with = 'MOVE "LiteSpeedDB" TO "D:\BackupLiteSpeedDB\LiteSpeedDB.MDF"'
, @with = 'MOVE "LiteSpeedDB_Index" TO "D:\BackupLiteSpeedDB\LiteSpeedDB_Index.NDF"'
, @with = 'MOVE "LiteSpeedDB_Log" TO "D:\BackupLiteSpeedDB\LiteSpeedDB_log.LDF"'

The DB is restored perfectly.

Sometimes you also get following error:
SQL LiteSpeed Error: XML returned from Engine was invalid

Let's Understad:  This error indicates that something happened at sql server end and litespeed is not able to understand what is returned from sql server.
How to Resolve: Most of the time excessive blocking, h\w issues and many other things under the sun. I would prefer to go for a quick health check and run the command again.

Also Read: Comparison between Litespeed Backup and Native Backup Compression


Regards,
http://tuitionaffordable.webstarts.com

No comments:

Post a Comment

Post a Comment