Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore Dbs on SQL Server 2012 via SQL Script taking more than 2hrs. Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 5:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 12:57 PM
Points: 13, Visits: 41
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)


Post #1432581
Posted Tuesday, March 19, 2013 9:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1432742
Posted Tuesday, March 19, 2013 11:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 12:57 PM
Points: 13, Visits: 41
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...
Post #1432806
Posted Tuesday, March 19, 2013 11:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:53 PM
Points: 196, Visits: 1,460
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.
Post #1432818
Posted Tuesday, March 19, 2013 1:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 12:57 PM
Points: 13, Visits: 41
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
Post #1432865
Posted Tuesday, March 19, 2013 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:53 PM
Points: 196, Visits: 1,460
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.
Post #1432868
Posted Tuesday, March 19, 2013 1:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 12:57 PM
Points: 13, Visits: 41
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)
Post #1432870
Posted Wednesday, March 20, 2013 3:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:46 AM
Points: 845, Visits: 2,331
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?
Post #1433509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse