MSSQL Backup Best Practices

SQL Server Management Studio provides sufficient tools for Backing Up and Restoring databases.

• https://technet.microsoft.com/en-us/library/cc180782.aspx

• https://support.microsoft.com/en-us/kb/2027537

Helpful Tips

• SQL Server backup will create .Bak files that can only be restored to the same version of SQL server or higher versions.

• Consider using compression for your backups especially if you store copies of the .bak files on a remote server or remote location

• Always discuss and decide how you plan to:

  • Do local backups for restoring data quickly if needed. 

  • Protect the business by ensuring there are not only local backups but also off-site backups available too. 

• Consider your configuration for Backups and Restoration by actually running through a practice restore.  Does your process take into account

  • The size of the .BAK file? 

  • The dataset that is most likely to need to be restored? 

  • The time required restoring?
    
    
    

• Remote Backup tools

  • There are several SQL Server Remote backup tools available.  When choosing a backup strategy, consider the amount of data that will be copied to your local SQL Server and the Remote Backup site.  Copying large amounts of data over the internet may not provide a timely database restore.

  • Consider creating a SQL Server backup to a different local drive.  This drive should be a different location than where the database files are located.  Once the .bak file is created, you can optionally copy this file to an off-site location for additional protection.  If the file is also kept locally, it can be quickly restored compared to copying large amounts of data across the internet thus impacting server downtime.

By reevaluating your backup processes you too may find significant advantages to modifying traditional AGRIS data management strategies popular with PSQL management. 

• Does it make sense to operate the Production Dataset in a separate database from the historical and month end datasets.

• How does this help you communicate to the business time required to restore?

• How does such a change impact the daily use of AGRIS?

• How does a change like this impact month end procedures?

• Do you gain flexibility in hardware/performance allocations by separating the datasets into multiple databases?  

• Would this make dataset/database management too complicated?

Questions like these should have answers that are relevant to your team's objectives before moving to MSSQL.