Description

This article describes recommended steps to ensure you can successfully back up your SQL Server. Specifically you will need to pre-configure your database via the SQL Management console to ensure both database and transaction logs can be backed up correctly.

Content / Solution:

  1.  Remote on to your Windows server via DNS name or IP address:


  2. Open the SQL Server Management Studio application:


  3. Make sure you are logging onto the correct SWL Server name, and authenticating correctly, either via Windows or Domain authentication:


  4. Find the SQL database that needs to be backed up by the Cloud Backup service. right click and select the Properties:


  5. Select the Recovery Model and change it from Simple to Full

    • SIMPLE


    • FULL


  6. Now change the security permissions on the database. This will ensure that the user account that will perform the backups is authorized:

    Note: Best practice is to create a user called DBAuser, with appropriate permissions(SQL sysadmin rights are required) to perform database backups. Ensure the account that is setup has the password set to NEVER EXPIRE.
     

  7. Select the New Login option under the Security section of the SQL management console:


  8. Click on the search button to find the newly created DBAUser account:


  9. Click on the Advanced button to find the DBAUser account. This is going to search the domain, so ensure it is a domain account, NOT a local account:


  10. Click on Find Now and you will see the new account as highlighted here:


  11. Highlight the user account and click the OK button:


  12. You should now see this screen in the SQL Management console. Click OK:


  13. You will now see this account displayed under the Security Logins section:


  14. Now find the sysadmin role under Server Roles and double click to edit the properties:


  15. Click on the Add button:


  16. Browse to see your DBAUser account:



  17. Click on the DBAUser account and click on the OK button:


  18. Then select OK again. This now ensures the DBAUser account is added to the svsadmin group within SQOL Management studio:


  19. If transactions logs are required as well for your backup strategy, we recommend these setup steps. In the SQL Management console go to the maintenance plans:


  20. Right click on the Maintenance Plan Wizard:


  21. Click NEXT:


  22. Enter a name of the job that sounds appropriate to your transaction logs. Click the change button:

    Note: AdventureWorks is just an example of a job name below.


  23. Now modify these sections:

    • Frequency to occur from Weekly to -> Daily

    • Daily frequency to occur -> Every Hour


  24. Click OK and then NEXT to continue:


  25. Select the Back Up Database [Transaction Log] and then click NEXT twice:



  26. Select the database on which you wish to have transaction logs created for. Select and change Backup set will expire. Also change the backup file for every database and the directory path.
     

  27. Select the drop down box and select the database that requires the Transaction logs to be backed up. Click OK to continue:

  28. Select the Backup set will expire after 8 days. This will take into account the full backups running weekly:


  29. Cloud Backup - Pre-requisites for SQL Server backup on a Windows Server_26
     

  30. Select the path where you want the logs to be written to. select OK and NEXT:



  31. Change the folder that the logs are written to, by clicking on the highlight button:


  32. Change the folder to the same folder as the Transaction logs as above. the click NEXT to continue:



  33. This should now finish the process:


  34. You should now see that the maintenance plan is setup: