We help IT Professionals succeed at work.

Timeout error occuring when running a SQL Restore script from web app

tatton777
tatton777 asked
on
Hello,

I'm running a program that creates new databases on the fly by restoring from a master.bak file. When I use this script against an unloaded database (empty tables) the script runs just fine. However, I now need it to restore a database that has some data in the tables. When I do this I get this error:

Encountered error while Creating Database Structure : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. RESTORE DATABASE is terminating abnormally. 10 percent processed. 20 percent processed. 30 percent processed. 41 percent processed. 51 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed.


Here is the code that creates the restore sql code:

   string populateDb = "RESTORE DATABASE [" + txtDbName.Text.Trim() + "] " +
                        "FROM  DISK = N'C:\\Program Files\\Microsoft SQL Server\\" +
                        "MSSQL.1\\MSSQL\\Masters\\msf_school.bak' " +
                        "WITH  FILE = 1,  " +
                        "MOVE N'msf_school' TO N" +
                        "'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\" + txtDbName.Text.Trim() + ".mdf',  " +
                        "MOVE N'msf_school_log' TO N" +
                        "'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\" + txtDbName.Text.Trim() + ".ldf',  " +
                        "NOUNLOAD,  REPLACE,  STATS = 10";
Comment
Watch Question

View Solution Only

Roshan Davis

Commented:
if anywhere you are mentioning the timeout value in your code, make sure that  the value is in MILLISECONDS, NOT in SECONDS.
TheMozz

Commented:
How long does the restore take normally?  This error looks like it is taking longer to do the restore than IIS wants it to. how long does it take to restore the db using SQL Management studio?
tatton777

Author

Commented:
It takes about ten seconds, sometimes a little longer.
TheMozz

Commented:
Just out of curiosity, did it create the db in question above?
tatton777

Author

Commented:
No the script did not actually make the db. Instead, when I looked at SQL Management Studio it showed the named database with the words (restoring) next to it. After giving it several minutes it still did not restore and I was forced to delete the 'restoring database' in order to stop it's process.
TheMozz

Commented:
My guess is you execute this code against the Master Database on the server. One way to do this would be to create a stored procedure inside the master db that takes the db name as a parameter and does the restore for you. That way should run much faster, and allow you to make changes to the way you restore db's if needed without recompiling your code or modifying the source.
Commented:
No answer given worked. I had the server techs work on this problem for hours. I eventually ended up discarding the VS 2008 Publisher and switched to FTP.