Database restore Archives

Introduction:

If you’re a database administrator, you know that restoring a database from a backup can be a time-consuming process. In the last article Top 5 Tips To Speed Up SQL Server Backups we discussed about tips to improve the SQL Database Backup Performance.

In this blog post, we’ll explore the top tips for making SQL database restores faster. These tips include using faster hardware, using multiple threads, using compression, using partial restores and more will help to improve SQL Database Restore Performance.

Use Instant File Initialization:

There are ways to optimize the process of creating new files for a database during a restore, particularly when restoring for the first time or using the WITH MOVE option. This feature, available in SQL Server 2005 and above, allows the operating system (which must be Windows XP or newer or Windows Server 2003 or newer) to quickly initialize the new files. To utilize this feature, the account that SQL Server is running under must have the appropriate security settings, such as being a member of the Windows Administrator group on the server or having the Perform Volume Maintenance Tasks security policy.

Use Multiple Files:

If you have multiple disk drives you can radically improve the speed of backups, and going the other way, restores. You do this by splitting the backup up amongst the multiple drives by using more than one backup file. It will require syntax changes to both your backup and your restore commands. This is directly related to throwing hardware at the problem, but instead of concentrating on faster disks, you’re concentrating on more disks.

Use multiple threads:

Another way to speed up database restores is to use multiple threads. This allows the restore process to run concurrently on multiple CPU cores, which can significantly reduce the overall restore time. To use multiple threads in SQL Server, you can use the WITH (MAXDOP) option in the RESTORE DATABASE statement. For example:

RESTORE DATABASE mydatabase
FROM mybackup
WITH (MAXDOP = 4)

Use compression:

Compressing the backup file before performing the restore can also help reduce the restore time. SQL Server supports both data compression and backup compression, which can significantly reduce the size of the backup file. To use compression in SQL Server, you can use the WITH COMPRESSION option in the BACKUP DATABASE statement. For example:

BACKUP DATABASE mydatabase
TO mybackup
WITH COMPRESSION

Use faster hardware:

One of the most obvious ways to speed up database restores is to use faster hardware. This could mean upgrading your server’s CPU, adding more RAM, or using faster hard drives or solid-state drives (SSDs). If you’re restoring a large database, these hardware upgrades can make a significant difference in the restore time.

Use a faster network connection:

If you’re restoring a database from a remote location, the speed of your network connection can impact the restore time. If possible, use a faster network connection or a direct connection to the server to reduce the restore time.

Recovery:

A significant portion of the restore process involves transferring data from the backup file to the data files, but there is one additional step at the end called recovery. During recovery, any transactions that were completed during the backup are applied to the data, while those that were not completed are rolled back. The amount of time this takes can vary significantly depending on the size and number of transactions. The Recovery Interval setting in SQL Server determines the frequency of checkpoints, with a default value of zero meaning that checkpoints occur at regular intervals determined by the SQL server. Changing this value to a higher number will result in more transactions occurring before a checkpoint, which can extend the recovery time. However, unless frequent checkpoints are causing performance issues, it is generally recommended to leave the Recovery Interval set to zero for faster recovery.

Conclusion:

Restores can be intimidating and stressful, but implementing the best practices outlined in this article can help reduce anxiety and improve the speed of the restore process. It is important to regularly practice restore operations to ensure that you are prepared in case of a real disaster. Lack of familiarity with the restore process is a common issue that can cause significant difficulties. By following these tips and regularly practicing restores, you can minimize the risk of encountering problems during a real restore operation.

Hello there, today in this article we are going to find out how to check the reason for database restore Stuck at 100 Percent. DBAs often get the question “how much time it will take for the database to restore? ”

Generally DBAs provide a rough estimation based on the size of the database or by checking prior restore time. But while restoring, it may take more time due to different reasons. You as a DBA need to know how to find out the exact reason and let customer know.

Best way to trace it using Extended event session. There are multiple ways of creating extended event sessions. I have used “Backup_restore_progress_trace” which documents the internal steps of the restore process. You can use the below query to create the extended event.

Query to Create Restore Extended Event

CREATE EVENT SESSION [Restore Trace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
    ACTION(sqlos.task_time,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\Restore Trace.xel') --Change the path as required
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Database Restore Stuck at 100 Percent — Fig:1

Now navigate to Extended Events > Expand Sessions > Right Click on the newly created Ex event and click on Start Session:

After this you are ready to start database restore. Once the restore is started you can come back and click on “View Target Data” as shown below. Straightaway to the right you will be able to see the default view.

Now in order to analyze the data, you need to add few columns to the view. Especially “database_Name” “operation_type” and “trace_message”.

For example refer the below screenshot to add “trace_message” to the table, highlight the row > Right Click > click “Show Column in Table”:

Now you can see the there are a number of steps in this process.

SQL Database Restore Stuck at 100 Percent — Fig: 4

The below screenshot shows 100% completion step. Important to realize here is 100% processed bytes is the same amount of data to be transferred from backup file as shown in Fig:4. Moreover you can see that VLF processing continues after that.

Now you know why restore status can stuck/take longer even though it shows 100% completion.

SQLGeeksPro

Tag: Database restore

Tips to Improve SQL Database Restore Performance