Post reply

Setting a database to "Restoring"

  • July 20, 2006 at 4:28 pm

    #62556

    Hi,

    I have a bit of a problem. We have implimented on a couple of instances that we have deployed a suppliers archiving solution on (and supposed to of been tested) - however, it has been failing on the insert, and then going on to delete the source records.

    Fine I thought - do selective restores and extract the data on another server. Then the bad news came from storage. They haven't been backing up the instance to tape since the beginning of the month - and didn't think to notify anybody. A bit of background on this. We have a SAN solution for the instances and every two hours, a snapshot is taken onto disk, and then the snapshot at @midnight is archived off to tape.

    Great - Not - then I checked and the flat file log backups had been backed up so I have had them restore the last tape backup to another server and all the log backups. Then I was stumped - the database attached cleanly, but then - hang on - how do I restore the LOGS. I was then informed that the storage team had to set a flag for restoring the database into restore mode - and guesse what - this can only be done by restoring over the Live Database - which is a large no-no

    So I am looking at "hacking" SYSDATABASES.STATUS column - but I can not find the & value to set the bit for RESTORING status. Does anybody know what this is.

    Also - another thought that has come to me is - can I backup the restored snapshot - do a restore and set no recovery for the restore and then restore the transaction logs to this database.

    Any help would be greatly appreciated.

    Cheers

    Steve

  • July 21, 2006 at 1:38 am

    #650595

    Anybody???

  • July 21, 2006 at 3:55 am

    #650618

    I have never tried doing what you want to do.  Is it possible to use a different SQL instance to do all your restore work.  When you have a clean database you can then take a backup for use on your live server.

    Your problems go outside the DBA area, and you should look for help from your management in resolving this.  A meeting between the DBAs and the Storage people, with a management mandate to find a solution to the problem could help move things on.  Also, when the problem is fixed a PIR should be done to identify how the problems can be avoided in the future.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • July 21, 2006 at 7:36 am

    #650684

    If the tape backup of the db was done via a SQL agent utilizing the SQL Server BACKUP mechanism and the tape software has the ability to restore the db WITH NORECOVERY (i.e. allow tran logs to be restored) you should be able to restore the full backup (WITH NORECOVER) Then any log backups executed after the full backup specifying WITH NORECOVERY for all but the last log restore which should be WITH RECOVERY.  If you happen to forget to change the last one you can execute a restore with recovery with no file/device specified.

    The SAN snapshots likely will be of little value except, maybe, to establish the db structure.

     

    HTH

  • July 22, 2006 at 2:59 pm

    #650933

    Hi

     

    thanks for your replies - I set the STATUS column to 12 on the sysdatabases entry for the database and it worked. I was then able to carry out a log restore with NORECOVERY - 3 times (had @ 380/400/420/450/470 to go because we would of needed to go back to the beginning for each days data) and then encountered

    Server: Msg 3456, Level 21, State 1, Line 1 Could not redo log record (290513:374:7), for transaction ID (0:11173219), on page (1:224546), database 'cr' (7). Page: LSN = (290513:152:1), type = 2. Log: OpCode = 5, context 4, PrevPageLSN: (290513:357:1). 

    http://support.microsoft.com/?id=329487

    but more than likely http://support.microsoft.com/kb/826433/

    so looks like we are screwed. Trouble is we have 30+ instances that looks like the backup methodology is screwed - and I am trying to get a PSS call raised to confirm if they are (I am a contractor - ends end of August - anybody interested - UK Northwest/Northwales based) )

    And they are not even ready to roll out SP4 yet.

    The HDS solution in place can only place the DB back in NORECOVERY mode when restored into the original location which means restoring over the existing DB and having an outage!!!!

    The comments about storage are true - This is a major outsourcing company - thouroughly ITIL based - and for something like this to happen is laughable.

    Now looking at Lumigent/Red-GATE to attach to the appropiate Log Backups to try and extract the data that way (that is if the company are willing to spend a few pounds/dollars - they are very tight relying on internal developed scripts to do the job).

    Cheers for all your replies

    SPL

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply