{"id":36,"date":"2011-03-07T05:50:51","date_gmt":"2011-03-07T05:50:51","guid":{"rendered":"http:\/\/support.cybersecure.com\/?p=36"},"modified":"2016-02-23T15:07:20","modified_gmt":"2016-02-23T05:07:20","slug":"microsoft-sql-server-backup-setup-best-practices","status":"publish","type":"post","link":"http:\/\/support.cybersecure.com\/?p=36","title":{"rendered":"Microsoft SQL Server backup setup &#038; best practices"},"content":{"rendered":"<h2>Creation of MS-SQL server backup set<\/h2>\n<h3>Prerequisites<\/h3>\n<ul>\n<li>CyberSecure Advanced installed<\/li>\n<li>SQL server 2005 and above<\/li>\n<li>SQL management studio<\/li>\n<\/ul>\n<h3>Pre-flight checklist<\/h3>\n<ul>\n<li><a href=\"http:\/\/support.cybersecure.com\/?p=2453\">Enable TCP\/IP<\/a> on the SQL server<\/li>\n<li>Set the SQL server to <a title=\"Enable MS-SQL server to listen on IP addresses\" href=\"http:\/\/support.cybersecure.com\/?p=2456\">listen on the correct IP address<\/a><\/li>\n<li>Enable <a title=\"How to enable mixed mode authentication in MS-SQL server\" href=\"http:\/\/support.cybersecure.com\/?p=2461\">SQL mixed mode authentication<\/a><\/li>\n<li>Create a dedicated <a href=\"http:\/\/support.cybersecure.com\/?p=2447\">SQL backup service account<\/a><\/li>\n<\/ul>\n<h3>To configure an MS-SQL backup set according to best practices:<\/h3>\n<ol>\n<li>Open CyberSecure Advanced by right-clicking the system tray icon<br \/>\n<span style=\"font-size: 29px; color: #000000; line-height: 43px;\"><a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/open-cybersecure.png\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-27\" title=\"open-cybersecure\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/open-cybersecure.png\" alt=\"\" width=\"243\" height=\"250\" \/><\/a><\/span><\/li>\n<li>Open the settings by clicking the &#8220;cog&#8221; icon<br \/>\n<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/open-settings.png\"><img loading=\"lazy\" class=\"alignnone size-medium wp-image-54\" title=\"open-advanced-settings\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/open-settings-300x219.png\" alt=\"\" width=\"300\" height=\"219\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/open-settings-300x219.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/open-settings.png 712w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>Add a new backup set by clicking the &#8220;plus&#8221; icon<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-add-backupset.png\"><br \/>\n<img loading=\"lazy\" class=\"alignnone size-medium wp-image-434\" title=\"adv-add-backupset\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-add-backupset-300x211.png\" alt=\"\" width=\"300\" height=\"211\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-add-backupset-300x211.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-add-backupset.png 992w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>Type a name for the backup set, select &#8220;MS-SQL Server Backup&#8221; from the drop-down list then click next<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-backupset.png\"><br \/>\n<img loading=\"lazy\" class=\"alignnone size-medium wp-image-435\" title=\"adv-select-sql-backupset\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-backupset-300x197.png\" alt=\"\" width=\"300\" height=\"197\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-backupset-300x197.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-backupset.png 995w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>The login ID and password of the SQL Server will be pre-populated to the system administrator \u201csa\u201d user. Please change if you&#8217;d like to use an alternative SQL user account (use only SQL authentication here, not Windows Authentication).<br \/>\n<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-server.png\"><img loading=\"lazy\" class=\"alignnone size-medium wp-image-436\" title=\"adv-select-sql-server\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-server-300x213.png\" alt=\"\" width=\"300\" height=\"213\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-server-300x213.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-select-sql-server.png 991w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>Select the Databases needing to be backed up &#8211; <strong>IMPORTANT: selecting the top-level server will mean all current &amp; future databases will be captured.<\/strong><\/li>\n<li>The next window will allow you to setup the schedule and backup types for the MS SQL Backup.\n<ol>\n<li>The recommended backup strategy is to have only a database backup (no transaction log backup) &#8211; 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.<\/li>\n<\/ol>\n<\/li>\n<li>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.<\/li>\n<li>Now click on the \u201cDatabase-Complete\u201d -&gt; Properties to set the schedule for the database backup.<br \/>\n<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-select-db-schedule.png\"><img loading=\"lazy\" class=\"alignnone size-medium wp-image-442\" title=\"adv-sqlserver-select-db-schedule\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-select-db-schedule-300x217.png\" alt=\"\" width=\"300\" height=\"217\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-select-db-schedule-300x217.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-select-db-schedule.png 749w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>Choose your desired schedule, ensuring that &#8220;Database &#8211; Complete&#8221; has been selected. We recommend performing a daily backup.<br \/>\n<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-set-db-schedule.png\"><img loading=\"lazy\" class=\"alignnone size-medium wp-image-443\" title=\"adv-sqlserver-set-db-schedule\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-set-db-schedule-198x300.png\" alt=\"\" width=\"198\" height=\"300\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-set-db-schedule-198x300.png 198w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-set-db-schedule.png 415w\" sizes=\"(max-width: 198px) 100vw, 198px\" \/><\/a><\/li>\n<li>Click OK and return to the schedule screen of the wizard<\/li>\n<li>If data seeding is NOT REQUIRED, then leave \u201cRun scheduled backup on this computer\u201d TICKED so that the automatic schedule will run and backup data automatically.<br \/>\n<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-run-scheduled-backup-ticked.png\"><img loading=\"lazy\" class=\"alignnone size-medium wp-image-445\" title=\"adv-sqlserver-run-scheduled-backup-ticked\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-run-scheduled-backup-ticked-300x216.png\" alt=\"\" width=\"300\" height=\"216\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-run-scheduled-backup-ticked-300x216.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-run-scheduled-backup-ticked.png 731w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>Continue on to the next screen. Leave the encryption settings to Default and click OK.<br \/>\n<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-encryption.png\"><img loading=\"lazy\" class=\"alignnone size-medium wp-image-446\" title=\"adv-sqlserver-encryption\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-encryption-300x216.png\" alt=\"\" width=\"300\" height=\"216\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-encryption-300x216.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-encryption.png 745w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>The backup set wizard will complete.<br \/>\n<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-wiz-complete.png\"><img loading=\"lazy\" class=\"alignnone size-medium wp-image-447\" title=\"adv-sqlserver-wiz-complete\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-wiz-complete-300x136.png\" alt=\"\" width=\"300\" height=\"136\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-wiz-complete-300x136.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-wiz-complete.png 388w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>There are just some additional best practices settings that need to be set.<\/li>\n<li>Once back in the main settings scree, ensure that the MS-SQL server backup set has been selected, then click &#8220;Options&#8221; in the left-hand pane.<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-options.png\"><br \/>\n<img loading=\"lazy\" class=\"alignnone size-medium wp-image-448\" title=\"adv-sqlserver-options\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-options-300x214.png\" alt=\"\" width=\"300\" height=\"214\" srcset=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-options-300x214.png 300w, http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-options.png 749w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>Select &#8220;In-File Delta&#8221; and use the <a href=\"http:\/\/support.cybersecure.com\/?p=1389\" target=\"_blank\">following settings<\/a><\/li>\n<li>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:\n<ol>\n<li>Enough free space to hold the entire SQL database<\/li>\n<li>Unrestricted permissions on the folder for the SQL server to spool out data<\/li>\n<li>It is highly recommended to create a dedicated D:\\CyberSecure-spool folder away from your main OS disk<\/li>\n<li>Network drives can be used, however there will be security considerations if the network device requires authentication to a domain.<\/li>\n<li>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.<\/li>\n<\/ol>\n<\/li>\n<li>Clicking &#8220;Change&#8221; will allow the temporary spooling location to be changed to a folder that is suitable for your environment.<a href=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-options-tempdir.png\"><br \/>\n<img loading=\"lazy\" title=\"adv-sqlserver-options-tempdir\" src=\"http:\/\/support.cybersecure.com\/wp-content\/uploads\/2011\/03\/adv-sqlserver-options-tempdir-300x213.png\" alt=\"\" width=\"300\" height=\"213\" \/><\/a><\/li>\n<li>Click OK and return to the main screen<\/li>\n<li>Always save settings to the offsite servers after completing any reconfiguration<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Creation of MS-SQL server backup set Prerequisites CyberSecure Advanced installed SQL server 2005 and above SQL management studio Pre-flight checklist Enable TCP\/IP on the SQL server Set the SQL server to listen on the correct IP address Enable SQL mixed &hellip; <a href=\"http:\/\/support.cybersecure.com\/?p=36\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[14],"tags":[27,43,44],"_links":{"self":[{"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=\/wp\/v2\/posts\/36"}],"collection":[{"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=36"}],"version-history":[{"count":22,"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":3298,"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions\/3298"}],"wp:attachment":[{"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/support.cybersecure.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}