The ‘public’ Server Role and ‘db_owner’ Database Role are the recommended SQL roles for Calxa users in our standard network database setup. This will give full control of the Calxa database including the ability to perform Calxa database upgrades while granting limited access to the server itself. There are however some differences in the Calxa backup and restore process and available options for SQL server users depending on their server roles. For a complete backup solution you should consult with the person who installed/configures your SQL Server. The SQL server role ‘sysadmin’ has full permissions and has access to all folders on the computer hosting your database and the SQL server databases.This support note will assist you with backing up your network database in Calxa, as well as manually backing up your network database through SQL Server Management Studio. If you are using a local data file you will need to refer to our Support Note article – Backup and Restore: Local Database.


Contents


Backup Network Database


  1. To backup a database as a ‘sysadmin’user click the Backup button on the ribbon:

    backup_network_1


  2. In the following screen select folder for your backup location and enter a backup name:

    backup_network_2 

     
  3. Click OK.

 

Restore Network Database


  1. To restore a database as a ‘sysadmin’ user access the Authentication screen by running Calxa or using the Calxa menu on the ribbon:

    restore_network_1


  2. Select Network database and then click the Restore Database button:

    restore_sysadmin_2


  3. Enter the SQL server connection details and click Login:

    restore_sysadmin_3



  4. Select a backup file from the directory that will open:

    restore_network_4


  5. Enter a new or existing database name to restore to and click the Restore button.

  6. Click Sign In on the Authentication screen to connect to your restored database:

    restore_network_6



Backup Without ‘sysadmin’ Server Role


You may still be able to perform an SQL server database backup without the ‘sysadmin’ Server role. As a minimum you require the ‘public’ Server Role and the ‘db_backupoperator’ and ‘db_datareader’ Database roles in User Mapping (More about SQL Server Database Roles).The Calxa recommended ‘public’ Server Role and ‘db_owner’ Database roles are sufficient to cover these minimum requirements. 

  1. To backup your SQL server database click on the Backup button on the ribbon:

    backup_sysadmin

     
  2. Enter the backup name or full path in the dialog that will open and click Backup:

    backup_sysadmin_1

  • Notes

    • SQL Server backups can only be saved to a local drive of the SQL Server instance
    • By default Calxa backups are saved to the default SQL Server backup folder, e.g. C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\
    • If you want to backup your file to another folder location that you have read and write permission on, then enter the full path to this folder, e.g. C:\Calxa\My backup folder.

 

Restore Network Database Without ‘sysadmin’ Server Role


For restoring to a new database you must have the ‘public’ and ‘db_creator’ Server Roles.

  1. To restore your backup access the Authentication screen by running Calxa or using the Calxa menu on the ribbon:

    restore_sysadmin_1


  2. Select Network database source and then click Restore Database button:

    restore_sysadmin_2


  3. Enter the SQL server connection details and click Login:

    restore_sysadmin_3


  4. Enter the backup file name or full path to the backup file:

    restore_sysadmin_4



  5. Enter database name to restore to and click the Restore button.

    restore_sysadmin_5

  • Notes

    • By default in ‘File name’ field Calxa shows the last backup location used by ‘sysadmin’. If you enter only a backup file name Calxa searches for the file in default SQL backup folder, e.g.  C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\.
    • You should append the full backup name. For example, ‘my_backup.bak’.

 


Create Backup Using SQL Server Management Studio


  1. Run Microsoft SQL Server Management Studio and connect to your SQL Server instance

  2. Expand the Database folder

    create_backup_2


  3. Right click on the database you wish to backup then select Tasks->Back Up…

    create_backup_3
     
     
  4. The default settings will suit most users however you will need to add a Destination to save your Backup. To add a destination click Add

    create_backup_4

     
  5. Click the ‘…’ button next to the File name field

    create_backup_5


  6. Navigate to the desired destination by expanding the tree, enter a File name and click OK

    create_backup_6

     
  7. Click OK again

  8. Select the correct destination if there is more than one and click OK

    create_backup_8
     
     
  9. Click OK to acknowledge that the backup completed successfully.

    create_backup_9



Restore a Backup Using SQL Server Management Studio


  1. Right click on the database you wish to restore and select Tasks->Restore->Database…

    restore_backup_1
     



  2. Select a destination for restore. This can be the same database or a new database

    restore_backup_2


  3. Check that the correct database and backup set is selected as the source and click OK

    restore_backup_3


     
  4. Click OK to acknowledge that the restore completed successfully

    restore_backup_4

 

Schedule a Backup


You can schedule database backups using SQL Server Agent; however the Server Agent service is not installed with SQL Express versions. For SQL Server Express users there are third party tools such as SQL Backup and FTP which can schedule database backups. This particular tool is free for up to 2 database backup schedules.  


Using SQL Server Agent

  1. Create Backup job as per Create Manual Backup

  2. Under the Script drop down menu select Script Action to Job

    schedule_backup_2
     
     
  3. Select Schedules on the left panel and click New to create a new schedule

    schedule_backup_3



  4. Populate the New Job Schedule form and click OK

    schedule_backup_4
     
     
  5. Select the schedule and click OK
  6. Click OK again if you wish to run the backup now

Note - To verify the backup job, expand SQL Server Agent in the Object Explorer, and then expand Jobs. When you do this, the SQL Server Agent service must be running. You newly created job should be listed. Double click if you wish to edit this job.