Comments
Sort by recent activity
I adjusted my role to sysadmin, but the error continues. I also tried using the RUN AS to modify the credentials. No dice.
I quickly toggled the database OFFLINE/ONLINE in case some phantom connection is in the way. Still no go.
When I look in activity monitor with no filter, there are no processes for my database.
I would note that there are 2 errors returned inside the command prompt.
The exact message is this:
Backing up CompressTest (full database) to:
L:\CompressRestore20120222\CompressTest_FULL.sqb
Error 880: BACKUP DATABASE permission denied in database: (CompressTest)
SQL error 924: Database 'CompressTest' is already open and can only have one user at a time.
SQL Backup exit code: 880
Last SQL error code: 924
/ comments
I adjusted my role to sysadmin, but the error continues. I also tried using the RUN AS to modify the credentials. No dice.
I quickly toggled the database OFFLINE/ONLINE in case some phantom connec...
I think I got it.
I was adjusting the database access from a query window and then changing my connection to Master.
USE [Master]
I thought this freed the single connection, but it does not. I had to 'DISCONNECT' my query session manually.
Now looking for a way to achieve this result without leaving the query window. / comments
I think I got it.
I was adjusting the database access from a query window and then changing my connection to Master.
USE [Master]
I thought this freed the single connection, but it does no...
We have extra variables in our environment for month end. I will likely have to chalk it up to a fluke. The event log was not very helpful and I can't find a reference to the backup log file. If it is an error in code(yours or ours), I am sure it will turn up again.
I'd prefer to have this record at least in case others are searching for the event. / comments
We have extra variables in our environment for month end. I will likely have to chalk it up to a fluke. The event log was not very helpful and I can't find a reference to the backup log file. If...
I have not resolved the issue. I have only created a second thread in another forum. You are welcome to keep an eye on both items.
The second forum is here. / comments
I have not resolved the issue. I have only created a second thread in another forum. You are welcome to keep an eye on both items.
The second forum is here.
I found that this particular server missed a SQL Backup update. It was running 6.3.
I updated to 6.5.1.9 AND UPDATED SERVER COMPONENTS
The next attempt at a restore, I adjusted some file locations in the 'move to' segment.
6 hours later...Success!
Now on to load testing.
(Note: I found the server components outdated when support requested a log file. I opened it and saw a 6.3.?.? So some credit goes to support ) / comments
I found that this particular server missed a SQL Backup update. It was running 6.3.
I updated to 6.5.1.9 AND UPDATED SERVER COMPONENTS
The next attempt at a restore, I adjusted some file locations...
I was able to execute
DBCC CHECKDB ('REPORTING01') WITH no_infomsgs
which returned only "Command(s) completed successfully."
I have had great success with smaller databases(Native 600GB-) They average 82% compression. (Meaning a 500GB db now has a footprint of 90GB). I think I will inquire on the Compression forum regarding large restores now that I don't believe the backup to be at fault. / comments
I was able to execute
DBCC CHECKDB ('REPORTING01') WITH no_infomsgs
which returned only "Command(s) completed successfully."
I have had great success with smaller databases(Native 600GB-) ...
I was unsuccessful in restoring the HBC file. The good news is that I received the same error(points for consistency).
Msg 5171, Level 16, State 2, Line 1
J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX is not a primary database file.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I'm leaning towards an issue with the source database. However, I am not in a position to attempt a native backup and test restore. I may have to leave this open ended and move my trials to another dataset and server. / comments
I was unsuccessful in restoring the HBC file. The good news is that I received the same error(points for consistency).
Msg 5171, Level 16, State 2, Line 1
J:\NDF\Reporting01_Compress_RemitPostBatc...
I was able to detach the 'broken' database using
exec sp_detach_db
This left the files intact. I then thought a
CREATE DATABASE
would resolve my issue, but I was met with a new error:
Msg 823, Level 24, State 6, Line 1
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'K:\IDF\REPORTING01_Compress_LogTapeCharge_Indexes.NDFX'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I am now attempting a backup of the 4 TB database to a .hbc (hyperbac compress) file.
Perhaps the restore will play nicer with it. / comments
I was able to detach the 'broken' database using
exec sp_detach_db
This left the files intact. I then thought a
CREATE DATABASE
would resolve my issue, but I was met with a new error:
Msg 823, L...
I was able to restore to .vmdf, .vndf, and .vldf files as a virtual restore. No errors were returned.
Over the weekend I created a new backup file on a new drive. The compress restore again failed with the same erorr.
SQL error 5171: J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX is not a primary database file.
It really looks like all the data is there as files, can I attach it or something? / comments
I was able to restore to .vmdf, .vndf, and .vldf files as a virtual restore. No errors were returned.
Over the weekend I created a new backup file on a new drive. The compress restore again faile...
I again received this error from SQL Backup:
SQL error 5171: J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX is not a primary database file.
I see some ads for 3rd parth software to repair(attempt) this error.
Does RedGate offer anything that would assist? / comments
I again received this error from SQL Backup:
SQL error 5171: J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX is not a primary database file.
I see some ads for 3rd parth software to repair(attemp...