SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore Dbs on SQL Server 2012 via SQL Script taking more than 2hrs.


Restore Dbs on SQL Server 2012 via SQL Script taking more than 2hrs.

Author
Message
SQLDW21
SQLDW21
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 53
We just upgrade our QA environment to SQL Server 2012 from 2008R2. Our standard process is to backup four databases from ETL server and restores each night on DB server. When we are on SQL 2008R2, the restore process was taking only 15-20min to restore four databases (total size = 130GB) but now we are facing issue where SQL Server2012 is taking same size of database almost 2:20hrs.

Currently, my Dev environment and production are still on 2008R2 and my restore job via SQL script finish in 15min but I am having hard time to figure out why 2012 version is taking 2+hrs?

Any help greatly appreciated.

SQL Server Version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 X64> (Build 7601: Service Pack 1)
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14905 Visits: 14396
Restoring a 2008 R2 backup to a 2012 instance means the restore process has to do an in-place upgrade of the involved database files which can add time to the restore process.

Are you watching the restore process, or do you have access to the output as it runs? If you supplied WITH STATUS then you'll see once it reaches 100% that upgrade messages start to be written. See if you can isolate which part is taking so long, the restore of the data files to disk, or the upgrade process that starts after that is done.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
SQLDW21
SQLDW21
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 53
Thanks OPC for your reply.

Currently, my both ETL and DB servers are on 2012 and I am taking FULL backup from ETL server and restore on DB server. Also, I am storing my backup on local drive.

Any thoughts...
Richard Moore-400646
Richard Moore-400646
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 1640
Just so I understand the scenario, you have the same processes running between 2 2008R2 servers and 2 2012 servers, where the source and target are the same database versions. If that scenario is true, there isn't anything related to SQL 2012 that would make the backup / restore process take longer. The issue is more likely related to the hardware that the SQL 2012 servers are running on, more specifically the disks associated with those servers. If you're using DAS (Direct Attached Storage) on the SQL 2012 servers, ensure that the raid controller(s) are using a write-back cache setting and not a write-through. Older IBM servers used to automatically ship with the battery backup unit for the raid controller, the newer servers don't. It's an additional option. I'm not saying this is you're problem, but it's a good place to start.
SQLDW21
SQLDW21
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 53
Richard,

Yes, it is on local storage and scenario is correct.

I need to check with my server team to find out the RAID config. Will post what I will find out later today.

Thanks
Richard Moore-400646
Richard Moore-400646
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 1640
The only other thing that comes to mind is if you're using compressed backups on the 2008R2 and not on the SQL 2012 servers.
SQLDW21
SQLDW21
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 53
Richard,

NO, they both are regular backups, and i tried with compressed backup but has the same situation. So, now i am using regular backup. I have asked my server team to give me difference between two server's RAID config. since both server drives are local. (Partitioned)
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1876 Visits: 2726
Have you checked the physical layer - are they all actually getting gigabit data transfer rates (try a Windows Explorer file copy) if you're restoring over the network.

Check the actual file sizes.

Is there excessive fragmentation of the free space on the 2012 restore server?

Does the 2012 restore server have a bad battery on the RAID card, or is cache otherwise disabled?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search