February 13, 2011 at 5:02 pm
Hello
I did a restore to ( The backup file is about 150 GB and sits in a portable hard drive attached to server )
After about 5 hours SQL sever shuts down. When I open SQl server again, the files are present but
SSMS says it is still restoring.
Please see attached piucture. You will understand what I am saying.
Any help welcome.
February 13, 2011 at 5:37 pm
Can you copy the file to the server and then run the restore?
February 13, 2011 at 6:24 pm
First - what do you mean by SQL Server shuts down? Performing a restore of a database will not cause SQL Server to shut down.
Second, what is the command you are using to restore the database? If you are using the GUI that might be the problem. If the GUI crashes - or you log off the server, or your session is interrupted the restore will fail.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 14, 2011 at 2:16 am
I did not use the GUI to do the restore.
I opened a query window and typed the "restore database ...."
command and clicked F5.
Shut down means you do not see SSMS in the desktop.
(That is as if someone went there are clicked on File->Exit and closed the application)
February 14, 2011 at 5:14 am
150gb should restore fairly quickly. What was the command you used to do the restore?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 6:31 am
restore database NGProd
from disk = N'D:\Backups\NGProd.bak' with file = 1,
move 'NextGen_System_Data' to 'D:\Databases\NGProd\NGProd_System_data.mdf',
move 'NextGen_Core_Data_1' to 'D:\Databases\NGProd\NGProd_Core_Data_1.ndf',
move 'NextGen_Index_1' to 'D:\Databases\NGProd\NGProd_Index_1.ndf',
move 'NextGen_Log' to 'D:\Databases\NGProd\NGProd_Log.ldf',
recovery,
stats = 10;
February 14, 2011 at 6:34 am
pankushmehta (2/13/2011)
Can you copy the file to the server and then run the restore?
No can not copy the backup file to the server.
Reason: Our sever has very limited space. That is why we decided to use a backup copy that is in a portable hard drive ( attached to the server via a usb cable )
February 14, 2011 at 6:38 am
mw112009 (2/14/2011)
pankushmehta (2/13/2011)
Can you copy the file to the server and then run the restore?No can not copy the backup file to the server.
Reason: Our sever has very limited space. That is why we decided to use a backup copy that is in a portable hard drive ( attached to the server via a usb cable )
Ah, that would help explain the speed. That's going to be slow access. Did it finish or is it still processing? I suspect, if SSMS crashed or was shut down, it might have interfered with the restore. Hard to know. Have you looked to see if you have any errors in the error log?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 6:46 am
The process is not slow.
I did the same ( using the same portable hard drive and the exact copy ) but using a server ( The server was a old PC with CPU of 3GHZ and 2GB RAM ) where I installed the OS and SSMS.
It worked just fine and completed within 1.5 hours ( or even less ).
So it is really not the portable hard drive that is causing the slowness.
The slowness is not really the issue here. Why is SQL sever crashing ( or shutting down ) after
5 hours almost at the very end of the "Restore" process. I expereicned this once then I deleted the Db and did a restore again and it did the same thing.
February 14, 2011 at 7:02 am
mw112009 (2/14/2011)
The process is not slow.I did the same ( using the same portable hard drive and the exact copy ) but using a server ( The server was a old PC with CPU of 3GHZ and 2GB RAM ) where I installed the OS and SSMS.
It worked just fine and completed within 1.5 hours ( or even less ).
So it is really not the portable hard drive that is causing the slowness.
The slowness is not really the issue here. Why is SQL sever crashing ( or shutting down ) after
5 hours almost at the very end of the "Restore" process. I expereicned this once then I deleted the Db and did a restore again and it did the same thing.
Wait though, let's be clear, you stated before that SQL Server itself did not crash, but that SQL Server Management Studio did. It's important that we're clear, because these are two very different things. One, SQL Server, is a service running on a machine that is hosting your databases, the other, SSMS, is just a GUI for manipulating that other service.
Something is up with your SSMS, assuming I'm reading what you've written correctly. But you've also got something up with your SQL Server instance. The backup command looks OK. What about ditching the stats, just so the restore operation doesn't have to communicate back to SSMS as it runs. You can monitor the status by running queries against sys.dm_exec_requests. that will show you if the process is being blocked, and if so, by what, plus it will show you the percentage complete for restore operations.
Also, are you up to date on service packs and cumulative updates? Both on the server and SSMS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 7:03 am
mw
restore database NGProd
from disk = N'D:\Backups\NGProd.bak' with file = 1,
move 'NextGen_System_Data' to 'D:\Databases\NGProd\NGProd_System_data.mdf',
move 'NextGen_Core_Data_1' to 'D:\Databases\NGProd\NGProd_Core_Data_1.ndf',
move 'NextGen_Index_1' to 'D:\Databases\NGProd\NGProd_Index_1.ndf',
move 'NextGen_Log' to 'D:\Databases\NGProd\NGProd_Log.ldf',
recovery,
stats = 10;
As an FYI - placing the log file on a seperate drive will speed up the restore. I saw a 250GB restore take a very long time because they had tempdb plus db plus t-log all on same drive.
Are you running the restore from the same box that SS resides on? There is the possibility that you are running into a memory issue and SS is closing. Try running the restore command from another workstation if possible.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 14, 2011 at 7:09 am
sjimmo (2/14/2011)
mwrestore database NGProd
from disk = N'D:\Backups\NGProd.bak' with file = 1,
move 'NextGen_System_Data' to 'D:\Databases\NGProd\NGProd_System_data.mdf',
move 'NextGen_Core_Data_1' to 'D:\Databases\NGProd\NGProd_Core_Data_1.ndf',
move 'NextGen_Index_1' to 'D:\Databases\NGProd\NGProd_Index_1.ndf',
move 'NextGen_Log' to 'D:\Databases\NGProd\NGProd_Log.ldf',
recovery,
stats = 10;
As an FYI - placing the log file on a seperate drive will speed up the restore. I saw a 250GB restore take a very long time because they had tempdb plus db plus t-log all on same drive.
Are you running the restore from the same box that SS resides on? There is the possibility that you are running into a memory issue and SS is closing. Try running the restore command from another workstation if possible.
The more drives and controllers you put into it, the faster the restore will generally be. This one is going to be fairly serial, everything going to a single drive.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 7:15 am
Let me make it simple.
When I started the "RESTORE.." I used the query window in SSMS.
Then 4 hours later when I logged back into the server I did not see SSMS running.
That's all that is what I have to say.
However, when I looked at the directories the restored files were there. When I started SSMS it shows the newdatabase ( but with a green arrow ) and a message saying "restoring..."
Ok, I need help on what SQL to run to check the interruptions you mentioned in your reply.
I have nto used that system view before.
February 14, 2011 at 7:24 am
mw
Let me make it simple.
When I started the "RESTORE.." I used the query window in SSMS.
Then 4 hours later when I logged back into the server I did not see SSMS running.
That's all that is what I have to say.
No reason to be so defensive. Everyone here is trying to help but some information is vague and we need to ensure that your true problem is understood as well as what steps have been accomplished.
One thing I don't see is what does the errorlog show as well as the system and application event view logs for the same period? There may be a hint there. Other things pointed out may not be the culprit at this time, but may be something to consider.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 14, 2011 at 7:32 am
To be honest I don't know how to read error logs or event logs. Is there a sql statement that you can send us please ?
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply