Will ROLLBACK work in RESTORE script if network problem occurs?

Question

Will ROLLBACK work in RESTORE script if network problem occurs?

JMI

SSChampion

Points: 11831
More actions
May 8, 2008 at 6:45 am

#190310

We get problems with our network.
Automatic RESTORE scripts fail in the middle:
_{Msg 3203, Sev 16, State 2, Line 1 : Read on "\\.....\USFPIMS\FullUSFPIMS20080502230837.BAK" failed: 64(The specified network name is no longer available.) [SQLSTATE 42000]}
Msg 3013, Sev 16, State 1, Line 1 : RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
I come in the morning and see USFPIMS (Restoring....)
The database wasn't fully recovered.
What if I put the Restore script inside a transaction?
Will it ROLLBACK everything if network problem occurs?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

I don't believe this will work since the restore physically is replacing pages. This isn't a logged operation AFAIK, because the log is being replaced as well. If this were something that could roll back, you'd need twice the data space in order to build the new database before the old one is removed.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

As a suggestion - first copy the back up files locally, then do the restore.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

If you have the room on the server you are restoring too, restore with a different name, then at the end of a succesfull restore, drop the original database and rename the restored one.

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

Ah, I like Ander's suggestion. Hadn't thought of that one.

JMI SSChampion Points: 11831 More actions · Answer 5

Copy the backup files to local first

and then run RESTORE looks like a good style, I agree.

But we only have 21 GB space on this DEV VMWare box.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

I guess I should have also said that the 3 systems I have seen this message on when doing restores from a remote server, there has been network issues every time. Been about 5 years since last time I saw one so can't remember the reason.

If you are short on space, get more. Easy answer 🙂 Or try to use a product that compresses the backup (I use LiteSpeed, but there are other products out there), so that the restores spends less time on network transport of the file. Also would make it a lot faster to copy the file from backup location to restore server. LiteSpeed is not cheap, but the ROI where I am now was about 2 months, our situation might be unique though as we never rotate tapes... The license is also less for development/test servers.

JMI SSChampion Points: 11831 More actions · Answer 7

Let's say you have 7GB backup file.

With LiteSpeed how small it could be?

And haw fast I can decompress it and start my RESTORE?