June 13, 2014 at 7:51 am
Hi,
While attempting to overwrite a dummy database with files from another database, my restore software threw the following error:
Description: Query Result [Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally. An exception occurred while executing a Transact-SQL statement or batch.].
The database was stuck in a restoring state after that. I attempted to restore from other LOG backups within the 15 minute window (needed 1pm, tried 12:45 and 1:15), and received this error:
Description: There is nothing to restore, database [database] is already up to date.
I finally got a restore to run from 1 hour prior to the target time, this time specifying the NO RECOVERY option, because I have a feeling the restore software doesn't default to restoring with that option even when restoring from logs.
Looking through the error logs, the information is minimal at the time of the restore trying to finish:
Date,Source,Severity,Message
06/13/2014 06:30:00,spid72,Unknown,Error while closing database 'databasename'. Check for previous additional errors and retry the operation.
06/13/2014 06:30:00,spid72,Unknown,Error: 947<c/> Severity: 16<c/> State: 1.
06/13/2014 05:35:01,spid64,Unknown,The database 'databasename' is marked RESTORING and is in a state that does not allow recovery to be run.
06/13/2014 05:35:01,spid64,Unknown,Starting up database 'databasename'.
When the database was initially created, this was raised: 00000000011E5BE1, but I can't find anything on any internet anywhere. However even with that error the database was marked online and started up successfully.
The business case for having to overwrite a dummy database with other files is that one has to be created through a web front end in order for the software people are using to query the database to register it in the software. We need the restore to fix a DML error made by a reviewer.
My question is: do I have any other options when this occurs? Is there a way to prevent it without relying on the NO RECOVERY option? And, I guess most importantly, did I do anything wrong?
Thanks
June 13, 2014 at 8:31 am
sqldriver (6/13/2014)
While attempting to overwrite a dummy database with files from another database
When you say files, do you mean you were restoring a backup in the usual way, or were you doing something else? Were you using SSMS? What syntax did you use?
June 13, 2014 at 8:52 am
Beatrix Kiddo (6/13/2014)
sqldriver (6/13/2014)
While attempting to overwrite a dummy database with files from another database
When you say files, do you mean you were restoring a backup in the usual way, or were you doing something else? Were you using SSMS? What syntax did you use?
I am restoring a mdf, ldf, and full text catalog. I am using CommVault, which is our backup/restore software. File names and locations were updated in the GUI, and the option to unconditionally overwrite was checked, because it won't restore over a DB without it. With RECOVERY was also specified.
The log and full text catalog were restoring to the same paths as before with altered database and file names, and the mdf was restoring to a new temporary LUN since it's a 5Tb database, so it had a new name, physical path, and file name.
Thanks
June 13, 2014 at 8:57 am
hi, Can you post the scripts that you are using to restore the database.
June 13, 2014 at 9:08 am
Hema kumar-337490 (6/13/2014)
hi, Can you post the scripts that you are using to restore the database.
Sure, here's what the restore software issued:
3556 2334 06/12 20:39:20 323781 CCvSQLVDIBase::StartDBRestore() - !!!!QUERY: [RESTORE DATABASE [databasename] FROM VIRTUAL_DEVICE = '1135a24d-18e8-4e87-b586-8e8f5681f829', VIRTUAL_DEVICE = '1135a24d-18e8-4e87-b586-8e8f5681f829_1' WITH NORECOVERY, MAXTRANSFERSIZE=2097152, BLOCKSIZE=65536, BUFFERCOUNT=20, REPLACE, MOVE N'old_databasename_log' TO N'F:\Logs\databasename_log.ldf', MOVE N'old_databasename' TO N'W:\Data\databasename.mdf', MOVE N'ftrow_old_databasename' TO N'G:\FTData\databasename'].
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply