How can I verify that a restore is completing? (4218394)

How can I verify that a restore is completing?

The process of restoring a backup file using LiteSpeed may be affected by the network environment, the server configuration, size of database, parameters of the actual restore.

Tools to verify that the restore is in process and functioning:

SQL Server creates the .mdf and .ldf files first (the timestamp will be the creation date/time and does not change); the data file will initialize at full size and it does not change size as the process continues. SQL Server will then grow (allocating the space) the database before actually asking LiteSpeed for any data. If LiteSpeed is hung, sqlserv.exe IO Write Bytes will not even increase. This may indicate a corrupted backup file.

During the VDI Timeouts, the sqlserv.exe IO Write Bytes will increase but there will be no activity for SQLLiteSpeed.exe. After VDI Timeouts are complete, the activity in the IO Read Bytes column for SQLLiteSpeed will increase.

If the database being restored is in a loading/recovery state and the restore is stopped by the user, the process must be started all over again. Even after SQL LiteSpeed is done restoring the database and a natural break is entered in the LiteSpeed log, the database must then replay the transaction log that was restored as part of the full restore.

To view activity on the server as it processes:

1. Open the Task Manager. Select Processes Tab.

2. Select View | Select columns and add in the columns I/O Read Bytes and I/O Write Bytes.

For a restore, the read bytes increasing for sqllitespeed.exe (as it reads from the backup file) and the write bytes increasing for sqlservr.exe (as it writes the database) will be displayed.

However, if LiteSpeed is still in the space allocation phase of the restore then activity may be minimal since SQL Server is processing the allocation of space.

Logging of the LiteSpeed may be enabled by either by setting logging to Verbose in the Restore GUI Wizard or using the parameter ,@logging=2, via script. The log will be written to %\Program Files\Imceda\LiteSpeed\SQL Server\Engine\Logs folder. The log is a text file and will be named similarly to: SLS_5_8_2006-11_02_01-AM.Log. The log file may be opened while the restore is in process, to verify LiteSpeed activity. While SQL Server is allocating the space for the database, there will be multiple entries to the log file indicating VDI Timeout; these are normal processing messages and indicate that the restore is proceeding. The time required for allocation may be the most time-consuming portion of the restore.

If using xp_restore or xp_restore_verifyonly, the parameter STATS= may be added.This displays a message each time another percentage completes, and is used to gauge progress. Ifpercentageis omitted, SQL Server displays a message after each 10 percent is completed (approximately).

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, the Database Engine reports at approximately that interval; for instance, instead of displaying precisely 40%, the option might display 43%. For large backup sets, this is not a problem because the percentage complete moves very slowly between completed I/O calls.

See:http://msdn2.microsoft.com/en-us/library/ms178615.aspx

The LiteSpeed extended stored procedure to display percentage completed:
EXEC master.dbo.xp_slsReadProgress
@database = { database_name | @database_name_var }

Also, sp_who may be verified for status of process.

Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. Data and log files are first initialized by filling the files with zeros when you perform a restore. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Instant file initialization is available only on Microsoft Windows XP Professional or Windows Server 2003 or later versions. For info on how to set the files to Instant Initialization, seehttp://msdn.microsoft.com/en-us/library/ms175935.aspx