Comments
Sort by recent activity
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...
On top of this error, I try to run the following code and get the below error.
RESTORE DATABASE [Reporting01_Compress] with recovery
sg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. / comments
On top of this error, I try to run the following code and get the below error.
RESTORE DATABASE [Reporting01_Compress] with recovery
sg 4333, Level 16, State 1, Line 1
The database cannot ...
We do not have enough disk space for an uncompressed restore of this backup.
I have been able to run a VERIFYONLY which returned an exitcode=0 and sqlerrorcode=0. This process takes 6 hours.
I have deleted the (In Recovery)database and will make a second attempt with the same file and process. / comments
We do not have enough disk space for an uncompressed restore of this backup.
I have been able to run a VERIFYONLY which returned an exitcode=0 and sqlerrorcode=0. This process takes 6 hours.
I hav...
I tested this locally and it seems to work fine.
Thanks. / comments
I tested this locally and it seems to work fine.
Thanks.
In production, we process each backup as part of that databases maintenance process. Many of these overlap in schedule. I have seen duration reductions when they are staggered.
In development and QA (20+ databases) I use the keyword "USER". This processes one after the other and works well with tags(<database>,<auto>). The only downside is a few errors have been hard to identify the source. /*FULL BACKUP FOR ALL USERS*/
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP USER DATABASES TO DISK = ''k:\DatabaseBAK\<database>\<AUTO>.sqb'' WITH COMPRESSION = 4, ERASEFILES_ATSTART = 1h, THREADCOUNT = 7"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
/ comments
In production, we process each backup as part of that databases maintenance process. Many of these overlap in schedule. I have seen duration reductions when they are staggered.
In development and...
If your backup is behind a password use syntax similar to
EXEC master..sqlbackup '-sql "RESTORE VERIFYONLY FROM DISK = [g:\backups\AdventureWorks_a.sqb] WITH PASSWORD=''mypass''" '
Be sure your password is between dual single quotes. / comments
If your backup is behind a password use syntax similar to
EXEC master..sqlbackup '-sql "RESTORE VERIFYONLY FROM DISK = [g:\backups\AdventureWorks_a.sqb] WITH PASSWORD=''mypass''" '
Be sure...
We have licensed clients(they care for the product and database) and managed clients(they pay us to do the above).
A licensed client is changing their contract and delivering their database to us. We now wish to verify they have the same indexes as our maintained production datasets. We also want to keep their other indexes(perhaps removed later) due to some unique usage of the product.
Is there a way to script out all 'create index' statements? (Or is this enhancement request required)
This would be a great benefit to an already valuable product.
Thank you. / comments
We have licensed clients(they care for the product and database) and managed clients(they pay us to do the above).
A licensed client is changing their contract and delivering their database to us. ...