Post reply

Backing up system databases to standby machine

  • May 22, 2008 at 10:55 am

    #191367

    Hi. I'm running SQL Server 2005, and I am trying to back up the system and user databases to a standby machine. I read that the master,msdb and user databases should be backed up at the same time. I was thinking of setting up one job through Management Studio with four steps to back up the master,msdb,distribution and user database every couple of hours.

    Some info- our publisher and distributor are the same server. The system and user databases are backed up nightly through full backups, but we currently don't have differential backups or transaction log backups, so we wanted to place frequent copies on the standby in case of emergency.

    I know this strategy is not great, but I am new at this and we are looking for a quick temporary solution. Any help or suggestions would be appreciated. Specifically, is there a better way of backing up the databases to the standby machine? Would 4 separate jobs be more efficient than one job with 4 steps? Should the user and system databases be backed up at the same frequency?

  • May 22, 2008 at 11:57 am

    #819300

    The system databases shouldn't be changing often, with the exception of distribution, which can always be recreated.

    You shouldn't need to make frequent backups of them

    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

  • May 22, 2008 at 12:29 pm

    #819313

    Thanks, Gail. Another question: this is taken from MSDN, Backing Up and Restoring Replicated Databases:

    "These databases should be backed up at the same time as each other and the relevant replication database. For example, back up the master and msdb databases at the Publisher at the same time you back up the publication database."

    How and when should these system and user databases be backed up at the same time?

  • May 22, 2008 at 12:41 pm

    #819319

    From your post, I take it you are talking about a DR setup?

    You should look into setting up Log shipping. Of course, that implies T-Log backups. The SSMS interface is good at leading you through this - just right-click on the database and select "Tasks|Ship Transaction Logs...". You will need to make sure your jobs and users are copied over to the DR server.

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

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