Copy Only restore taking really long time

Question

Copy Only restore taking really long time

jshurak

SSCrazy

Points: 2763
More actions
June 27, 2011 at 10:36 am

#253400

Are copy only restores supposed to take longer than normal restores?
I had restore a production database to a test server, so I used the Copy_Only option for both the database and log. The backups themselves took about 30 minutes.
The restore is running at over 90 minutes so far. The .bak size was about 45GB and the .trn was about 13GB. I've restored this database before and it only took about 30 minutes. That was without using the Copy_Only option.
Thoughts?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

could your test server be low on disk space? maybe it's taking a long time trying to allocate disk space to the backup before it can ctually do the restore. SQL tries to grab as amny contiguous checks of memory it can, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Usually test server have much slower disks. This MAY help a little bit.

http://www.sqlskills.com/blogs/kimberly/post/instant-initialization-what-why-and-how.aspx

jshurak SSCrazy Points: 2763 More actions · Answer 3

Yeah....just going to take a while. The backup files are located on the same drive as the db files...so I'm sure that's not helping

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

Two other things.

1. Is this a new database? In which case, the log has to be written with 0s, which can take a long time.

2. Instant file initialization on? If not, the data file is written out as well

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

jshurak (6/27/2011)
Are copy only restores supposed to take longer than normal restores?
I had restore a production database to a test server, so I used the Copy_Only option for both the database and log. The backups themselves took about 30 minutes.
The restore is running at over 90 minutes so far. The .bak size was about 45GB and the .trn was about 13GB.

I've restored this database before and it only took about 30 minutes. That was without using the Copy_Only option.
Thoughts?

Copy_Only will n't do anything with the restoration time.

you have restored both the restoration same time and same backup size ( both with/without copy_only)

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

Copy_Only option is available for the Backup without interrupt the database backup chain

Example

1-Full Backup without copy_only option

2-Full Backup with Copy_only Option

3-Differential Backup

Then you will restore the first full backup without copy_only and then differential third one and copy_only option can restore on a separate server separately

My Blog
www.aureus-salah.com

jshurak SSCrazy Points: 2763 More actions · Answer 7

Thank you all for the replies. Alas, the story continues.

I chose Copy_Only option because the client needed the production database restored to a training server and I didn't want to interrupt the standard backup chain. This is an occasional request from the client.

After letting the restore run for two and a half hours, I canceled and took a new standard backup. Unfortunately, this also took exceptionally long and after trying various options, I decided to let it run over night. 4:30 this morning it was still running. Five and half hours!

I opted to create an identical database and restore to that, then rename the databases accordingly. This restore took maybe 15 minutes.

What I noticed, and believe to be the issue, is that when I executed the sql statement:

ALTER DATABASE MyDB

SET SINGLE_USER the database would hang. I was able to set it to restricted mode through the SSMS properties window and alter it back to multi_user through tsql.

I believe this was the issue as my restore had ALTER statements to set the database to single_user and multi_user access before and after the restore, respectively.

Has anyone encountered anything like this? I ran the same restore script for a smaller database on the same instance without issue.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 8

What I noticed, and believe to be the issue, is that when I executed the sql statement:
ALTER DATABASE MyDB
SET SINGLE_USER
the database would hang. I was able to set it to restricted mode through the SSMS properties window and alter it back to multi_user through tsql.

May be users are connected the DB when you tried to restore thats why its hang.

Use the with rollback immediate option .It'll rollback the uncommited trans.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Lowell SSC Guru Points: 323518 More actions · Answer 9

yep i think muthukkumaran Kaliyamoorthy nailed it; someones in the database...maybe even you in the same or another SSMS window.

ALTER DATABASE MyDB

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jshurak SSCrazy Points: 2763 More actions · Answer 10

Yeah, I didn't use rollback immediate, I'll keep that in mind for next time. Thank you so much.