The Xopero application allows to direct backup of Microsoft SQL databases for users of Advanced license. It is possible to backup only local Microsoft SQL server - both the database server and Xopero application must be installed on the same computer.
Xopero supports versions Microsoft SQL Server from 2005 to 2016.
Create the backup set
In order to create the Microsoft SQL database backup set, in client application, you should run the Backup set wizard by clicking on New backup set button in My backup sets tab.
In the Backup set wizard mark Advanced copy and select SQL Server from dropdown list. Don’t forget to define the backup set name.
Then in SQL Server section define following parameters which are required to connect to the database server:
- Database server – select from dropdown list name of the Microsoft SQL server instance that has to be backed up.
- Trusted connection – checking this option will cause authenticate to the database server as Windows user. User and Password fields will be inactive. The Windows username and its password should be provided in Advanced tab of the Backup set wizard in section Privileges.
- User – the Microsoft SQL username.
- Password – the password for provided user.
|The user, provided in Xopero application, must have permissions to database backup on Microsoft SQL server.|
|If the Windows user authentication is used to Microsoft SQL Server it is required to provide its username and password in section Privileges of Advanced tab in Backup set wizard.|
If above data has been provided properly the application will connect automatically to database server and will display all available database as dropdown list in Database field. Select one of them or check the All databases what will cause that all databases from the server will be included to backup.
|When all databases option is checked each new database from the server will be included automatically to backup.|
When all data are set correctly click on the Test connection button to check connection to the database.
Next define Temporary directory, it is a folder where the database dump, from Microsoft SQL Server, will be stored. Select also one of the following Database script type that defines the type of database dump:
- Full - every backup will create a full dump of the database,
- Differential - the database dump will contain only the differences that have been made since the last full database dump from server,
- Transaction log - contains all records of transaction log which have been generated since its last copy or last full database backup. It lets to restore the database to given point in the time.
In case of select the differential dump of database select whether and how often the full dump of database has to be made.
|We recommend to perform full database dumps, its frequency depends on database size and differences that are made between backups.|
The transaction log is an incremental backup, so when the database is restored from transaction log it is necessary to have all the transaction log records to given point in time. These records are included in the chain of log backups.
Restore Microsoft SQL database
To restore the Microsoft SQL database, in addition to the Xopero application, it is also necessary tool for database management - Microsoft SQL Server Management Studio Express which is available to download under: https://www.microsoft.com/en-us/download/details.aspx?id=29062.
Download the database backup
In order to download the Microsoft SQL database backup run the Xopero application and then go to the Restoring tab. All backed up databases will be in Microsoft SQL Server branch.
Expand the branch and mark the checkbox at the database which has to be restored and the click on the Restore selected button.
|In order to restore selected version of the database right click on its name and from context menu select Show file versions.|
The file restore settings window will be displayed where you have to select the location where the database backup file has to be saved. From this directory it will be also restored to the database server.
Restore the database from downloaded file
In order to restore the downloaded Microsoft SQL database, to the database server run Microsoft SQL Server Management Studio.
After correct login to the Microsoft SQL server right click on Databases branch and, from context menu, select Restore Database option.
In displayed window select Device option and then choose previously downloaded database backup. Then in Destination field select the database to which backup will be restored (it is necessary). If the database does not exist, it is required to create it.
|If the restored database file contains incremental backup, there will be available list of included backups. They correspond to each version of the database, making it possible to restore previous state of the database.|
The transaction log can be restored only after restoration of the full database version from given period of time or by using all transaction logs since last full database copy. It is required to restore them in order from oldest to newest. It could be necessary to use option RESTORE WITH NORECOVERY.
Click OK in the bottom part of the window to start the restore process to the Microsoft SQL server.
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'testowa_baza' database
The error occurs while trying to restore the database which already exists in the database server. To solve it go to Options page in the database restore window and select the Overwrite the existing database. It overwrites the existing database.
System.Data.SqlClient.SqlError: This differential backup cannot be restore because the database Has been restored to the correct earlier state.
The error occurs when Xopero application has been made the differential database dump and another application made full dump the database. To restore the database it is required to use its last full dump and then the differences could be restored from Xopero backup file.
System.Data.SqlClient.SqlError: The file: 'C:\....' cannont be overwritten. It is beign used by database 'BazaDanych'.
The error occurs while database file could not be overwritten, because it is still in use. To solve this problem go to the Files page in the database restore window and change the file name to which database has to be saved. Click on field Restore As and provide new filename.
In case when Xopero application performs differential database dump and user wants to make additional backup using other software, it is possible only when the software lets to copy the database in COPY_ONLY mode. It cause performing full copy without changing the data that is necessary for Xopero application to backup the database.