LiteSpeed for SQL Server(7.5) How to Diagnose & Troubleshoot Restore

(1)

LiteSpeed for SQL Server(7.5)

(2)

• Learning objectives- Slides # 3

• Restore functional overview- slides # 4

• Common restore issues- slides # 8

• Common restore error explanation- slides # 10

• Troubleshooting restore checklist-slides # 16

• Prerequisites for contacting support-slides # 20

Slide Index

(3)

Learning Objectives

• Upon completion of this presentation, the student should be able to:

–

Identify the actual problem the user has reported.

–

Create and read LiteSpeed log file.

–

Search knowledge base.

–

Identify, troubleshoot, and resolve common LiteSpeed restore problems.

–

Solve the issue or gather the necessary information to escalate the case.

How To Diagnose & Troubleshoot

(4)

(LiteSpeed) Functional Overview

Restore

Restore components:

1. LiteSpeed console.

2. Restore with script.

exec master.dbo.xp_restore_database @database = N'TestDB1' ,

@filename N'C:\LSBACKUP\TemplateFolder\DailyDB\ALVHLY2_DailyDB_full_1351710237.bak', @filenumber = 1,

@with = N'REPLACE‘,@with = N'STATS = 10',

@with = N'MOVE N''DailyDB'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL10.BOSTON\MSSQL\DATA\TestDB1.mdf''', @with = N'MOVE N''DailyDB_log'' TO N''C:\Program Files\Microsoft SQL

Server\MSSQL10.BOSTON\MSSQL\DATA\TestDB1_1.ldf''', @affinity = 0,@logging = 2

GO

1. Command prompt.

Restore the Northwind database from the backup device c:\temp\Northwind.bak using variables: sqllitespeed.exe -R Database -D Northwind -F "C:\temp\%D.bak"

(5)

(LiteSpeed) Functional Overview

Restore - continues

Restore:

1. Restore from console.

Select target database for restore.

Option to kill all the current connections

2. Restore script GUI sample;

exec master.dbo.xp_restore_database @database = N'DailyDB' ,

@filename = 'C:\LSBACKUP\TemplateFolder\DailyDB\ALVHLY2_DailyDB_full_1351710237.bak', @filenumber = 1,@with = N'REPLACE',@with = N'STATS = 10',

@affinity = 0,@logging = 2 GO

(6)

(LiteSpeed) Functional Overview

Restore from command line - continues

Restore:

1. Command prompt restore

2. Sample script;

Restore the Northwind database from the backup device c:\temp\Northwind.bak using variables:

(7)

(LiteSpeed) Functional Overview

Restore by creating a Microsoft format file - continues

Restore:

1. Create a Microsoft compatible format.

2. Sample script;

(8)

Automated Restore

• Enterprise Edition feature

• New feature to easily restore the most recent backup of a database

• Great way to keep non-prod environments refreshed

• Great way to test restores in non-production environments

• No need to know in advance the actual backup file names

• Scans folders/subfolders with optional file extension filter or use static file name

• Supports Full, Full + Diff, or Full + Diff + T-Logs

• Works with new Restore as Compressed engine support

• Database can be automatically dropped after restore (restore testing)

• Integrated directly into the Restore Wizard

• Available for scripting through LiteSpeed API

(9)

Automated Restore – API

• New xp_restore_automated extended stored procedure

exec master

.

dbo

.

xp_restore_automated

@database

=

N'AdventureWorks2008R2Test'

,

@backuppath

=

N'C:\Backup\AutomatedRestore'

,

@backupextension

=

N'bak'

,

@checksubfolders

=

1 ,

-- @backuppath = N'C:\Backup\AutomatedRestore',

-- @backupextension = N'bak',

-- @checksubfolders = 1,

@sourceserver

=

N'XPS435\SQL2008'

,

@sourcedatabase

=

N'AdventureWorks2008R2'

,

@backuptype

=

N'tlog'

,

-- full, diff, tlog

@affinity

=

0 ,

@logging

=

0 ,

@dropdatabaseonsuccess

=

1 ,

@withreplace

=

1 ,

(10)

Automated Restore – Final Notes

• TSM is not supported (disk restores only)

• The target server must have access to the disk backups (shared location)

• Only one password is allowed for encrypted backups (if passwords differ

between Full, Diff, and T-Logs, then limit use to the Full restore option)

(11)

Common Restore Issues



Failed to create VDI, error 995 or x80770004



Failed to login.



Restore failed due to LSN out of sync.



Restore failed due to a corrupt backup.



Failed to create a Microsoft compatible due to login

(12)

Common Restore Issues - Continues



Unable to access backup file.



Copy file to target drive corrupt the backup file.



Did not have correct path for MDF and LDF.

(13)

(14)

Failed to create VDI - error 995 or x80770004



This means that SQLServer memtoleave does not

have enough resources to produce a VDI package.

Sometimes this is normal, as there is enough resources available, and

the backup will still complete.



Other times, you may have to allocate more virtual

memory to SQLServer.



Temporary workaround is to reduce the maxtransfersize parameter

from your backup setting or restart SQL Server services.

(15)

Failed to login



This can happen when restoring a full backup and it failed to connect to

the instance. You should check user permissions or the SQL Server alias

may no longer be available.



A similar problem can also happen if the LiteSpeed engine is no longer

available.

(16)

Restore failed due to LSN out of sync.



You are trying to restore a backup and the Last Sequence Number from

the backup file did not match the current restore state LSN.

(17)

Restore failed due to a corrupt backup.



The restore failed, because the database is no longer able to perform a

header or restoreverify check, see sample command below;



EXEC xp-restore-headeronly @filename = 'D:/backup/test.BAK'

(18)

Did not have the correct path for MDF and LDF.



When performing the restore, it did not have the correct MDF and LDF

files.



The target SQL Server path is not correct or permission is not granted to

create the new database.

(19)

(20)

LiteSpeed Logs



Various LiteSpeed processes send messages to the Server

about the operations taking place, and report any problems

or errors encountered during these operations.

The system logs reside in the .../Logs directory on the LiteSpeed Server (where ... represents the

LiteSpeed installation directory).

This directory can be relocated using the registry.



The log files are stored in binary format and named SLS*.log

with latest time.

For example, when a backup with verbose turn on, it will create a log like ‘SLS 2012-10-29

23-30-03 7320.Log

System Logs are useful for tracking activities and troubleshooting problems.

(21)

Using the Knowledge Base



Go to:

http://support.software.dell.com/netvault-litespeed-for-sql-server/kb

(22)

LiteSpeed Trace



When LiteSpeed logs don’t give you enough information to identify the

problem, it may be necessary to get a trace. When escalating a case, a

trace is usually required.



Don’t ask for a trace unless you need it. Unless you have experience

with reading the trace, it will be difficult to make sense of it.



A trace may use up ample amounts of disk space. Please verify the

trace directory listed in the General tab has plenty of space. Only

generate trace/debug files when requested.

(23)

(24)

• Problem Description

• Diagnostic logs, screenshots, etc.

• Environmental details (LiteSpeed version, SQL Server version,

and Service pack applied.)

• Issue severity and customer business impact, timeframes, etc.

• If a performance issue, provide specific details as specified in

the case.

(25)

FAQ

What is required in order to open a support case?

When opening a support case, we request



the environment information

(Operating systems, versions, software/database versions, etc),

(26)

Summary

In this lesson we learned:



How to restore a backup and read LiteSpeed restore log.



Search the knowledge base