We help IT Professionals succeed at work.

SQL 2008 R2 Long Restore.

hevsys
hevsys used Ask the Experts™
on
I have one install of MS SQL 2008 R2 on several servers. One of the servers I installed a named instance. Then I found that a named instance was not network aware. So I uninstalled MS SQL 2008 R2 and reinstalled as default instance. The server works fine, but to restore my 70 gig database now takes over 24 hours, when on the servers that I did not make this mistake it takes about 30 min. The backup of the large database takes about the same amount of time to backup.

Is there some setting I can check to speed up the restores?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Harish VargheseProject Leader

Commented:
Hello,

Reinstalling SQL Server may not be the reason for this delay.
How is the overall performance of this server? Is your backup copy located on the same server? or somewhere in the network?
How much time does it make a copy of this 70 GB backup on your server itself?

Thanks,
Harish
Check out this http://msdn.microsoft.com/en-us/library/ms190954.aspx.  The information below is in the Optimizing Restore Performance section.

Optimizing Restore Performance
Restoring a database or differential backup consists of four steps:

Creating the database and transaction log files if they do not already exist.

Copying the data from the backup devices to the database files.

Copying the transaction log from the transaction log files.

Rolling forward the transaction log, and then restarting recovery if necessary.

Applying a transaction log backup consists of two steps:

Copying data from the backup devices to the transaction log file.

Rolling forward the transaction log.

Restoring a database file consists of two steps:

Creating any missing database files.

Copying the data from the backup devices to the database files.

Commented:
Well i dont think it has something to do with the reinstallation process or the installation itself.

I believe it could be related to hardware related things.
May I ask the following:

1. Are you running in a RAID enviroment?
2. How many ressources are used by the SQL server and the server?
3. Do you have allocated memory for the SQL server?

4. Start a database Backup on the SQL server and run the following script on the master database:

SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc

This script  will give you the elapsed time and the percentage completed by the SQL server of the backup process.

I want to see how much it spools in percentage per seconds.


Bear ind mind that the script above does not make any change or modification of your enviroment -it is simply a SQL Select statement.

Author

Commented:
Hi, Both servers are running the same hardware and software. They are in RAID.

Memory usage = 274000 kb

No, default install of both SQL and the dB. Both are the same.

The results of your query are here.

PrismPA_SQL      1      11      9.054879      BACKUP DATABASE [PrismPA_SQL] TO  DISK = N'd:\Backup\PrismPA_SQL_backup_Delta_04182012.bak' WITH NOFORMAT, NOINIT,  NAME = N'PrismPA_SQL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Thank you,
Jeff


Well i dont think it has something to do with the reinstallation process or the installation itself.

I believe it could be related to hardware related things.
May I ask the following:

1. Are you running in a RAID enviroment?
2. How many ressources are used by the SQL server and the server?
3. Do you have allocated memory for the SQL server?

4. Start a database Backup on the SQL server and run the following script on the master database:

Author

Commented:
Hi,
 The server is performing fine, yes the backup is on the same drive as the dB in the server not external. It takes about 10 min to copy the backup file.

Thank you,
Jeff



Hello,

Reinstalling SQL Server may not be the reason for this delay.
How is the overall performance of this server? Is your backup copy located on the same server? or somewhere in the network?
How much time does it make a copy of this 70 GB backup on your server itself?

Thanks,
Harish

Author

Commented:
Using the same script that databoks gave me durring a restore of the same dB that took 10 min to back up gives me this output to restore.

master      3      1714      0.2261048      RESTORE DATABASE [Prism_AFS_SQL] FROM  DISK = N'D:\Install\PrismPA_SQL_backup_Delta_04172012.bak' WITH  FILE = 1,  MOVE N'PrismPA2_SQL' TO N'D:\Prism_Data\Prism_AFS_SQL.mdf',  MOVE N'PrismPA2_SQL_log' TO N'D:\Prism_Data\Prism_AFS_SQL_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

Jeff
Top Expert 2012

Commented:
One of the servers I installed a named instance. Then I found that a named instance was
not network aware.

You may want to double check that, as it is clearly not true.

Author

Commented:
Hi acperkins, Thank you for the comment, but I could not connect that install to a Coldfusion server. When I went into the MSQL configuration tools the protocal TCPIP was active but no port assigned like my other installs. I tried to assign port 1433 but it did not work. So I uninstalled MS SQL and installed it as a default instance and I could map to the server fine from Coldfusion. So, I am sorry if the statement is not "true" but from my end, it is what happened.

Jeff
Top Expert 2012

Commented:
In order to use a named instance you need to be prepared to make a simple change to your connection string from the default.  If you are unable to do that than certainly your application will not support named instances.  It is just the way you phrased it made it sound that you cannot use named intances on a network, which if you think about it does not make a lot of sense considering the vast majority of instances are in fact named instances.
Commented:
This issue is resolved. The solution was a setting in the Raid controller.

The controller is a 3ware 9750-4i Raid Controller card. The a 'StorSave Profile' setting was at balanced. We set it  to performance. Now the 70 gig database restores in 19 min instead of 40 hours.

Thank you all for your help.

Author

Commented:
It was mine LOL