Post reply

Bringing System to its Original state

  • December 15, 2008 at 10:20 pm

    #195213

    Hello,

    What all database backups do I need to have to recover SQL SERVER to its original state after a system crash....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • December 15, 2008 at 10:42 pm

    #912942

    You need backups of your system and user databases.

    If you have .bak files, those are usually backups.

    Is the system completely dead?

  • December 15, 2008 at 11:02 pm

    #912951

    I need to prepare a Backup strategy for our application to work with in an hour, If in case client system goes dead....Normally I take backup of user databases daily...recently one of our client formatted the server due to some issue..and told us to bring back the machine to its original state in an hour or 2....I restored the user databases....but as I din't had the backups of system databases...the reports and jobs were to be done thru scripting.....I want to know what all system databases shud be backed up and restored to get the system to it original state with minimum downtime.......

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • December 16, 2008 at 12:05 am

    #912957

    I need to prepare a Backup strategy for our application to work with in an hour, If in case client system goes dead

    Well thats a question that you need to discuss with the client. There are several solution for high availability, cheapest ones are log shipping and replication. But at least you'd require another server (preferably of similar specifications) to do so.

    If you just want to take backups and restore, the time restoration duration will depend primarily on the database size. you cannot restore terabyte-sized database in an hour.

    You need to backup master and msdb databases besides user databases.

    Also you should keep the recovery model to FULL. The frequency of full backups should be decided by the database size and appropriate time when users' activity is low. backup your transaction log every 15-20 minutes if the transaction volume is huge. You need to take a call on how much data you can afford to lose (if system crashes) to decide this frequency.

    If you decide to take full backups on weekly basis, you should also use differential backups.

    Again all these depend on your db size, transaction volumn on ur db, time range of the day when users are connected etc...


    Pradeep Singh

  • December 16, 2008 at 12:16 am

    #912961

    ps (12/16/2008)

    You need to backup master and msdb databases besides user databases.

    And model if you've made any changes to that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 16, 2008 at 12:29 am

    #912967

    Yup!!


    Pradeep Singh

  • December 16, 2008 at 1:02 am

    #912974

    Is there any specific order for restoring system database.....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • December 16, 2008 at 1:41 am

    #912983

    Not exacly, but they're very different from restoring user databases. Books Online has a good section on restoring the various system databases.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 16, 2008 at 5:25 am

    #913066

    GilaMonster (12/16/2008)

    ps (12/16/2008)

    You need to backup master and msdb databases besides user databases.

    And model if you've made any changes to that.

    How about resource system database? does it get automatically backed up with the master? not sure on this.....

  • December 16, 2008 at 8:39 am

    #913206

    Nope, Resource needs to be copied separately, but it's basically not changed with anything you do other than patches.

    So if you need to restore everything, you want the Resource to be at the proper patch level. In DR, you'd install SQL, apply patches, then restore master/msdb, etc. Resource would be at the proper level based on patches.

    http://www.mssqltips.com/tip.asp?tip=1544

  • December 17, 2008 at 4:35 pm

    #914240

    Thanks for the extra info Steve.....

  • December 18, 2008 at 4:39 am

    #914396

    Just make sure that the build for the backup and restore servers are same before you restore the backups of the system databases. This won't work in case there is some difference between the two.

  • December 18, 2008 at 5:28 am

    #914424

    The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

    The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure.

    The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf.

    SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

    For more info : http://technet.microsoft.com/en-us/library/ms190940.aspx

Viewing 13 posts - 1 through 12 (of 12 total)

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