Unable to restore BAK file - SQL2008 1

[juniper911]

Hi all,

I am trying to restore 2 databases in SQL Server 2008, both of the restore processes generate the same error message.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)

I have been trying to figure this out and have not have any joy yet. Did all the basic checks, restarted the server, turned off the firewall on the server, checked permissions.

I obviously missed something so any suggestions are welcomed gratefully.

J

 

[markros]

Are you trying to restore them in a script? How big are these BAK files?

PluralSight Learning Library

 

[juniper911]

No, manually - Right click on Databases and then Restore. I've managed to restore one of the databases, the one that I am having trouble with is just over 8 GB. The one that finally restored was 10 MB.

I'm a n00b to SQL, I say I am restoring but effectively moving 2 databases from a another system to here. So on a different SQL server, I did a backup to .bak and then moved the file on this SQL server where I am trying to do a restore.

 

[juniper911]

Is it worth increasing the timeout value?

In Options > Designers > Table and Dataase Designers it is set to 30 seconds.

 

[markros]

I agree with increasing query timeout and also I suggest to generate the script from the Restore dialog and then try to execute this script instead of doing it from the SSMS. I had similar problems before when I tried restoring from the interface and it timeout out, but worked as a script.

----------------
BTW, what is your SQL Server version? In SQL Server 2008 you can use compression option when generating backups - it does created them 4-5 times smaller than without compression.

Take a look at this blog post

http://blogs.lessthandot.com/index....testing-backup-compression-in-sql-server-2008

PluralSight Learning Library

 

[juniper911]

Markros

Thanks for that, however when I click on the .bak file, that is when things hang for a while and then I get the timeout message. So I cannot click on the script button, well I can but it says I need to specify a restore source.

I suppose I could write a script to restore it? Google will help me on that!

I increased the timeout to 90 but that did not make a difference.

Good article about the compression - will bear that in mind.

 

[markros]

http://msdn.microsoft.com/en-us/library/ms186858(SQL.90).aspx

PluralSight Learning Library

 

[juniper911]

Ran a simple restore command that I ended after 17 minutes, it kept saying 0 rows even after that time.

I also noticed in the event logs this error message repeated several times during the retore period. I will check the hardware on the machine.

SQL Server has encountered 6 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [V:\Databases\example.mdf] in database [<Database name is not available>] (6). The OS file handle is 0x0000000000000A54. The offset of the latest long I/O is: 0x000000d47a0000

 

[juniper911]

Ignore my previous comment, I think it was a red herring, I still have not solved it but am progressing!

I ran RESTORE VERIFYONLY FROM DISK and got this error

The file on device 'X:\example.bak' is not a valid Microsoft Tape Format backup set.

In Event Log, I am seeing this.

RESTORE detected an error on page (0:0) in database "EXAMPLE" as read from the backup set.

I then ran RESTORE HEADERONLY

and the BackupName is coming up with **INCOMPLETE**

I am guessing the database is corrupt?

 

[markros]

Is it possible to make another backup of that database (but make sure to use compression option, if it's available in your SQL Server installation)?

PluralSight Learning Library

 

[SQLBill]

You might try to just recopy the original backup file again. It may have gotten corrupt while you were copying it from one server to the other.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[juniper911]

Someone offline suggested that the IO error I mentioned in an earlier post could be a problem and causing the timeout when trying to do a restore by GUI.

I am going to copy the bak file to the C drive of the server rather than the other drive which is on the SAN.

If I was to change the default location of the Databases as test to the local storage on the server would that cause issues with the existing System databases? Would they still be running OK?

 

[juniper911]

*RESOLVED*

The IO error I was seeing did infact have something to do with issue. After further trouble shooting it turned out the problem was with a faulty fibre cable!! For some reasonm the server kept going to the SAN over the same faulty cable, disabled the port which forced the other port to be used and found that this was much better.

Hope this helps someone in the future.