Trying to restore a database
-
Hi, everyone,
I am trying to restore a database that I backed up from a different server and restrore on another server. The backup file is backuped to the server to which i wish to restore. so I do not think it is a problem in regards of having permissions. But when I run the Restore command :
RESTORE DATABASE dbase FROM DISK = 'G:\FullBackups\DBPROD02\Uniflow_FMH\dbase_20050106_1249_FULL.bak'
WITH MOVE 'dbase_Data' TO 'G:\Program Files\Microsoft SQL Server\MSSQL\Data\dbase.MDF',
MOVE 'dbase_Log' TO 'G:\Program Files\Microsoft SQL Server\MSSQL\Data\dbase_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\dbase_UNDO.DAT'
When I execute the above it runs for about eight minutes and then I get this:
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
Server: Msg 3158, Level 16, State 1, Line 1
Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection Broken
Anyone have an idea of what going on. This restore command script has worked before in the morning. Is it possible I am trying to execute this at the wrong time of day when traffic may be heavy?
thanks
tom
-
I always use the restore wizard for this.
So long as the filepath matches the existing target database, and you use 'force restore', it should be fine.
-
Why are you attempting to create a standby file, especially on a remote server?
-
I fixed it by restoreing it through the wizards. Very strange since the above code worked before. Thanks everyone.
The reason as I understand it is that if the main server or primary goes down we can switch over to the standby. We logship to the standby. Why do you ask?
-
You didn't mention that you wanted the database in standby mode, so I thought you were adding options that you didn't need.
Are you actually creating the undo file on a remote server?
-
Yes. It is.
-
Is there a server related issue that makes you want to store that remotely?
-
I am still perplexed by the message and I get from the script. Can not figure out why it would one time and not another. Strange!!!
-
My guess would be a timeout or permissions issue related to the remote share (are you connecting using the same information for both restores?)
-
No, just can't afford the down time. At least this business wants to keep it to a min. I am a newbie to administration so I am learning as I go. However, I do have a lot of development experince. SQL that is.
-
Yes, I have considered that this may be the problem. Why then would it work one time and not another. Thats what has me crazy.
-
I would always keep the undo file on the same server as you are restoring the database to. Not doing so could prevent you from recovering correctly when you really need to (if the remote server was down or there were timeout issues with the share).
-
Are you sure you have enough disk space? You could have large amount of uncommitted transactions.
Or the other possibility -- are you sure the servers versions are the same?
From BOL: STANDBY is not allowed when a database upgrade is necessary.
Just throwing out my $0.02.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Nick, I do restore the file to the same server as where the undo file is. Perhaps I misstated earlier.
Jim, pretty sure there is enough dish space but you bring up a valid point about the uncommited. I was not aware of the versions. I will check that also. Thanks Guys.
-
The script you posted indicated that you were using a remote share to place the standby file, that's what had me confused. I didn't realise that share was on the same machine.
RESTORE DATABASE dbase FROM DISK = 'G:\FullBackups\DBPROD02\Uniflow_FMH\dbase_20050106_1249_FULL.bak'
WITH MOVE 'dbase_Data' TO 'G:\Program Files\Microsoft SQL Server\MSSQL\Data\dbase.MDF',
MOVE 'dbase_Log' TO 'G:\Program Files\Microsoft SQL Server\MSSQL\Data\dbase_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\dbase_UNDO.DAT'
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply