Microsoft SQL Server backup setup & best practices

Creation of MS-SQL server backup set

Prerequisites

  • CyberSecure Advanced installed
  • SQL server 2005 and above
  • SQL management studio

Pre-flight checklist

To configure an MS-SQL backup set according to best practices:

  1. Open CyberSecure Advanced by right-clicking the system tray icon
  2. Open the settings by clicking the “cog” icon
  3. Add a new backup set by clicking the “plus” icon
  4. Type a name for the backup set, select “MS-SQL Server Backup” from the drop-down list then click next
  5. The login ID and password of the SQL Server will be pre-populated to the system administrator “sa” user. Please change if you’d like to use an alternative SQL user account (use only SQL authentication here, not Windows Authentication).
  6. Select the Databases needing to be backed up – IMPORTANT: selecting the top-level server will mean all current & future databases will be captured.
  7. The next window will allow you to setup the schedule and backup types for the MS SQL Backup.
    1. The recommended backup strategy is to have only a database backup (no transaction log backup) – this keeps things simple and ensures a complete backup each time. If you have a requirement for a transaction log backup, please keep following this article, then add in the transaction log backup schedule.
  8. In the schedule settings, click on the transaction log schedule and REMOVE it. If your database is very large, or you have a specific requirement to backup transaction logs, see this article for information on performing transaction log backups.
  9. Now click on the “Database-Complete” -> Properties to set the schedule for the database backup.
  10. Choose your desired schedule, ensuring that “Database – Complete” has been selected. We recommend performing a daily backup.
  11. Click OK and return to the schedule screen of the wizard
  12. If data seeding is NOT REQUIRED, then leave “Run scheduled backup on this computer” TICKED so that the automatic schedule will run and backup data automatically.
  13. Continue on to the next screen. Leave the encryption settings to Default and click OK.
  14. The backup set wizard will complete.
  15. There are just some additional best practices settings that need to be set.
  16. Once back in the main settings scree, ensure that the MS-SQL server backup set has been selected, then click “Options” in the left-hand pane.
  17. Select “In-File Delta” and use the following settings
  18. The SQL server backup will spool out to a temporary folder before backing up offsite. It is critical that this folder has the following attributes in order for the backup to function correctly:
    1. Enough free space to hold the entire SQL database
    2. Unrestricted permissions on the folder for the SQL server to spool out data
    3. It is highly recommended to create a dedicated D:\CyberSecure-spool folder away from your main OS disk
    4. Network drives can be used, however there will be security considerations if the network device requires authentication to a domain.
    5. If Cybersecure-spool is being set to a network location, ALWAYS use UNC pathnames (e.g. \\srv01\backup\Cybersecure-spool) instead of a mapped drive letter. The reason is that windows mapped drives exist only within the session in which they were created, so when the user logs out of the system, the mapped drive will disappear.
  19. Clicking “Change” will allow the temporary spooling location to be changed to a folder that is suitable for your environment.
  20. Click OK and return to the main screen
  21. Always save settings to the offsite servers after completing any reconfiguration