Results 1 to 3 of 3
  1. #1
    MrTee Guest

    Question MSSQL How to minimize time when Restoring databases.....

    Hello,

    Every week, I get a back up of production (Live) data base that i restore to development (Test) enviroment. Sometimes it is same some time it has some changes. but every time, the restore takes a huge bulk of time. Is there a way I can minimize that time ? Just waste of time.

    Shouldn't there be an intelligent way of checking if the rows haven't changed ? or what are the changes ?

  2. #2
    RonR Guest

    Default Backup and Restore MSSQL database - common practices !!

    I have two suggestions for you.

    1. Schedule a task that keeps checking for backup file every fixed interval. When file exist kick start the restore. Do it at night .... less load on server.

    2. Talk to the person doing the backup.... If that is you, Here is something for you if that is someone else tell them you need "differential backups "

    What is differential backups ?
    A differential backup is a type of data backup that preserves data, saving only the difference in the data since the last full backup. The rationale in this is that, since changes to data are generally few compared to the entire amount of data in the data repository, the amount of time required to complete the backup will be smaller than if a full backup was performed every time that the organization or data owner wishes to back up changes since the last full backup. Another advantage, at least as compared to the incremental backup method of data backup, is that at data restoration time, at most two backup media are ever needed to restore all the data. This simplifies data restores as well as increases the likelihood of shortening data restoration time. [Source : Wiki]

    Here is a Sample syntax for a Differential Database Backup


    SQL Server 2005
    Code:
     
    BACKUP DATABASE AdventureWorks TO DISK = 'Drive:\Temp\DatabaseBackups\AdventureWorks_Full.bak'
    GO 
    BACKUP DATABASE AdventureWorks TO DISK = 'Drive:\Temp\DatabaseBackups\AdventureWorks_Diff_1.bak' WITH DIFFERENTIAL
    GO
     
    BACKUP DATABASE AdventureWorks TO DISK = 'Drive:\Temp\DatabaseBackups\AdventureWorks_Diff_2.bak' WITH DIFFERENTIAL
    GO
    SQL Server 2000
    Code:
    BACKUP DATABASE Northwind TO DISK = 'Drive:\Temp\DatabaseBackups\Northwind_Full.bak'
    GO 
    BACKUP DATABASE Northwind TO DISK = 'Drive:\Temp\DatabaseBackups\Northwind_Diff_1.bak' WITH DIFFERENTIAL
    GO
     
    BACKUP DATABASE Northwind TO DISK = 'Drive:\Temp\DatabaseBackups\Northwind_Diff_2.bak' WITH DIFFERENTIAL
    GO

  3. #3
    MrTee Guest

    Default

    Thanks RonR ..... I will look into that ..... Just cleaning my things to dos before holiday seasons.


    Happy Holidays!

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •