SQL SERVER – Mirrored Backup and Restore and Split File Backup

Introduction

This article is based on a real life experience of the author while working with database backup and restore during his consultancy work for various organizations. We will go over the following important concepts of database backup and restore.

  1. Conventional Backup and Restore
  2. Spilt File Backup and Restore
  3. Mirror File Backup
  4. Understanding FORMAT Clause
  5. Miscellaneous details about Backup and Restore

Conventional and Split File Backup and Restore

Just a day before working on one of the projects, I had to take a backup of one database of 14 GB. My hard drive lacked sufficient space at that moment. Fortunately, I had two 8 GB USB Drives with me. Now, the question was how to take a backup in two equal sizes, each of 7 GB, so I can fit them on each USB drive. Well, conventional backup takes one large backup in one file. However, SQL Server backup command can take backups in two or more split parts.

Read Complete Article here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

3 thoughts on “SQL SERVER – Mirrored Backup and Restore and Split File Backup

  1. Hi,

    I would like to know more about backup and restore. I need to separate operational db and history db. The operation db will store only data for 6 months. The history db will store everything. How can I implement this ?

    Thanks
    Ye

    Like

  2. @ Ye,

    This process is called as Archiving.

    Generally all companies do this process to improve performance of application, if you have Millions of records, then select statements would take plenty of time to display output, so archive unwanted data and put historical data into other (History) database.

    If you are using any COTS product, an application which is not build in house, but you bought it from outside ( Vendor product). Then generally Vendors provide script to archive data.

    If application is built in house, then you can do this,
    SQL Server by default does not provide any feature that I know of.. which you can use to archive history data. You need to write script /DTS / SSIS Package that will load data from your main database to history database. And schedule this script /DTS / SSIS Package monthly / bi-monthly ( Of-course during maintenance window).

    While loading data into History Database make sure you check the order to load ( Load data first in parent table and then child table, Foreign Key relation Ship). and while deleting data from your main database, delete first from child tables and then from parent tables.

    This should take care of Archiving.

    ~ IM.

    Like

  3. Hello
    I want to backup my server(sqlremusServer) into external harddrive
    and i need to put time schedule on it to backup every week
    and i want to backup trasactLog for every 3 days?

    my main requirement is to maintain sqlserver
    I thought maintainnig sql server is backing up without lossing the dat

    I am new to this work?

    could any one help regarding this
    thanak u

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s