November 29, 2011 at 5:00 am
Hello,
I'm sure this question has probably been asked many times, but as I am new to SQL I wonder if someone good person out there could please indulge me with either a pointer to a source that will help or some simple advice ...
I am running SQL Server 2008R2 and have tried to restore a sample database I have. There are two backups contained within the backup set disk file and I wanted to restore the earlier backup from this set. To achieve this, I had to opt to restore from a device rather than the Database and point the device to the disk file containing the backup. Whether this is good practice or not, I cannot say, but I wanted to restore an earlier version of the database and the default options wouldn't show me all available backup instances within the set.
Anyway, moving on. The database in question is set to operate in the "Simple" recovery mode (I wanted to keep my learning straightforward initially).
Upon starting the recovery once I'd selected the earlier backup, the restore failed after a few seconds with the following message:
Msg 5120, Level 16, State 106, Line 1
Unable to open the physical file "F:\Data\FSFG1". Operating system error -1071906798: "0xc01c0012(failed to retrieve text for this error. Reason: 317)".
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The particular folder/filereferenced above exists (although I don't know why you'd need a file/folder present in order to restore it). I tried again after removing the offending file/folder in case of some obscure permission issues but with the same result.
I've also tried selecting the "overwrite .." check box with the same results.
Now, the database is stuck in "Restoring .." mode and I don't know what to do to alter this.
Also, is it normal for a database to be left in a "Restoring .." state when a restore fails?
Any help appreciated.
November 29, 2011 at 5:31 am
raotor (11/29/2011)
Hello,I'm sure this question has probably been asked many times, but as I am new to SQL I wonder if someone good person out there could please indulge me with either a pointer to a source that will help or some simple advice ...
I am running SQL Server 2008R2 and have tried to restore a sample database I have. There are two backups contained within the backup set disk file and I wanted to restore the earlier backup from this set. To achieve this, I had to opt to restore from a device rather than the Database and point the device to the disk file containing the backup. Whether this is good practice or not, I cannot say, but I wanted to restore an earlier version of the database and the default options wouldn't show me all available backup instances within the set.
Anyway, moving on. The database in question is set to operate in the "Simple" recovery mode (I wanted to keep my learning straightforward initially).
Upon starting the recovery once I'd selected the earlier backup, the restore failed after a few seconds with the following message:
Msg 5120, Level 16, State 106, Line 1Unable to open the physical file "F:\Data\FSFG1". Operating system error -1071906798: "0xc01c0012(failed to retrieve text for this error. Reason: 317)".
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The particular folder/filereferenced above exists (although I don't know why you'd need a file/folder present in order to restore it). I tried again after removing the offending file/folder in case of some obscure permission issues but with the same result.
I've also tried selecting the "overwrite .." check box with the same results.
Now, the database is stuck in "Restoring .." mode and I don't know what to do to alter this.
Also, is it normal for a database to be left in a "Restoring .." state when a restore fails?
Any help appreciated.
Check that the F: drive actually exists and is accessible
To take the database out of the restoring state, run the following
restore database DBNAME with recovery - Don't have a connection to this database when running this.
November 29, 2011 at 5:39 am
Yep, drive F: is alive and kicking and I can see no issues with it.
By the way, everything is running locally. i.e. all drives and my SQL Server/SSMS are all on my PC.
November 29, 2011 at 5:43 am
It could be a security issue. Does the account that your SQL Server instance is running under have access to that drive? You might, but does SQL Server?
"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
November 29, 2011 at 5:48 am
raotor (11/29/2011)
Yep, drive F: is alive and kicking and I can see no issues with it.By the way, everything is running locally. i.e. all drives and my SQL Server/SSMS are all on my PC.
Check which service account runs SQL server, you can get this in SQL Config manager
Make sure this account has access to the F: Drive
November 29, 2011 at 5:50 am
Yes.
The F: drive is where this database also has a partitioned table which is fully accessible. The F:\Data\FSFG1 file/folder was an old filegroup used for evaluating the FILESTREAM functionality in SS2008R2, but has no actual data in it ands has been removed in the current database configuration.
If I remove F:\Data/FSFG1 before the attempted restore then it actually gets created before the restore fails.
November 29, 2011 at 5:53 am
Hmmm... Not sure then. Is FILESTREAM still configured to that drive? It might be interfering with using it for data files. Can you restore the database using WITH MOVE and put the data file on a different 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
November 29, 2011 at 6:53 am
Well, after looking up the syntax to restore the database with the MOVE option and directing the restore to another drive for both the data and log files the result is the same with the same error message produced.
I will look into the FILESTREAM settings on the database and see if I need to disable/enable them (if already disabled by me before).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply