Når du benytter MS SQL eller MySQL, ved du allerede, at det er en database, hvor Structured Query Language bruges som værktøj til at hente vigtige data. Derfor er du måske også klar over, at det koster dyrt, hvis den vigtige data forsvinder eller havner i de forkerte hænder.
Hos Online Backup er du i trygge hænder. Vi sørger for, at du har en online backup af din database – uanset om det er Maria Database, MS SQL Database eller MySQL Database.
Vi hjælper med online backup af databaser:
→ Understøtter transaction backup
→ Backup af kørende databaser
→ Understøtter fuld / logbackup
→ Lokal kopi for gendannelse
→ Beskyt vigtige data
Læs mere om, hvorfor online backup af MS SQL eller MySQL er en god idé for din virksomhed.
SQL server backup
Hvorfor er det egentlig, at backup er så vigtig? Når du benytter en SQL-database, behandler den ofte personfølsomme data og vigtig struktureret data. På samme tid er databasen vigtig, hvis din virksomhed skal opskalere og beholde samme ydeevne.
Med disse faktorer in mente, er det vigtigt, at du ikke mister din data. Enhver enhed kan desværre komme ud for vandskade, brand, tyveri eller forfald, og så er en backup-løsning essentiel.
Online backup af database sikrer din data
Hos Online Backup giver vi dig en nem løsning, der sikrer, at du hele tiden har en sikkerhedskopi af din database. Vores smarte opsætning kan skræddersys efter dit behov. På den måde kan du selv vælge hvad, hvornår og hvor ofte din backup af din database skal køre.
Vores cloud backup laver en komprimeret og krypteret backup af din database. Derefter tager vi en backup af din backup, som giver dig dobbelt så meget tryghed.
Flere fordele ved automatisk backup af database:
→ Vigtige data sikkerhedskopieres
→ Vælg selv, hvor ofte der skal køre backup
→ Prøv det gratis i 45 dage
Med online backup af din database, er du sikker på, at din data ikke går tabt.
Fordele ved vores online backup af MS SQL og MySQL
Hos Online Backup sikrer vi din data med backup-løsninger til din database.
Frequently Asked Questions
What are the backup modes VSS and ODBC with an MSSQL backup
Online-Backup.dk allows you to back up databases in your Microsoft SQL Server with the MS SQL Server Backup Module, which provides you with a set of tools to protect your MS SQL Server, whether in “VSS Backup” mode or “ODBC Backup” mode.
VSS-based backup utilizing the Microsoft SQL Server VSS Writer to obtain a consistent snapshot of the MS SQL databases, no spooling / staging of database file(s) is required during the backup process.
Temporary Folder Requirement
- Location for temporary folder
The temporary directory folder is used by Pro Backup for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows and or database.
- Temporary folder capacity
With VSS-based backup, the disk space of the temporary folder required for storing the VSS image is significantly smaller than using the ODBC spooling backup method. As the extra space is not required to hold the full database.It is recommended that the temporary directory should have at least free disk space of 50% of the total database size. The rationale behind this recommended free disk space is the default in-file delta ratio settings is 50%, therefore Pro Backup could generate incremental or differential delta file(s) of up to 50% of the total database size. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
- Fast and minimal interruption
The database snapshot capture process is fast and can take place on a running server, as you may continue to work when the snapshot capturing is taking place, there may be another process that holds your input in some memory section until the snapshot capture is completed. That said, the whole snapshot capture is fast, so there is no need for you to stop working and it causes minimal interruption to your business operation.
- Significantly lesser disk burden
VSS Snapshot typically requires much less additional disk space than clones which is the traditional backup method by spooling database into the temporary folder. Oftentimes, the capacity of the database to back up is huge and therefore the temporary folder would overload with the equal or even larger disk space if traditional backup method is used. By utilizing the VSS technology, it helps your system greatly reduce disk capacity burden and promote optimized performance.
- No Transaction Log Backup
MS SQL does not support transaction log backup when VSS is used, therefore, transaction log backup will have to be done manually.
- Workaround is time consuming
In order to truncate the transaction logs, you have to either change the Recovery model to Simple or perform a manual log truncation, which could be time consuming.
Transaction Log Handling
VSS based backup no longer requires backup of the transaction log files, however for databases configured in either full or bulk-logging recovery model, this may eventually result in transaction logs filling up the available disk space on the volume of the MS SQL Server. https://technet.microsoft.com/en-us/library/cc966520.aspx
To prevent this from occurring, it is recommended to change the recovery model of database selected for backup to simple recovery model. Refer to the following steps for details:
- In SQL Server Management Studio, expand Databases, select a user database, or expand System Databases and select a system database.
- Right-click the corresponding database, then click Properties to open the Database Properties dialog box.
- In the Select a page pane, click Options.
- The current recovery model is displayed in the Recovery model list box. Modify the recovery model by selecting Simple from the model list.
By using the ODBC mode for MS SQL backup, database files are spooled to a temporary directory before being uploaded to the backup destination.
Temporary Folder Requirement
- Location for temporary folder
The temporary directory folder is used by Pro Backup for storing; the database files, incremental/differential delta files, and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows and or database.
- Temporary folder capacity
ODBC backup requires a significantly larger disk space of temporary folder as it need to store the database files spooled during the backup process. It is recommended that the temporary directory have disk space of at least 150% of the total database size. For each database backup, Pro Backup will spool the database files to the temporary directory before they are uploaded to the backup destination. Also, additional space is required for in-file delta generation the default in-file delta ratio settings is 50%, therefore Pro Backup could generate incremental or differential delta file(s) of up to 50% of the total database size. The actual disk space required depends on various factors, including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
- Support Automated Transaction Logs Backup
Schedule backup of transaction log can be configured so that the transaction logs can be backed up periodically and the transaction logs are truncated automatically after each backup job.
- Support Point in Time Recovery
The ability to restore to a point in time for all of your transaction log backups.
- Support Backup of High Transaction Databases
For databases which supports a high number of transaction which may require frequent backups. Transaction log backups at regular intervals are more suitable and less resource intensive than VSS based backups, i.e. transaction log backup every 60 minutes, 30 minutes, 15 minutes etc depending on the database transaction volume.
- Large disk space required
Since the database files will be spooled to a temporary folder before uploading to backup destination, investment on hard disk could be high if your MS SQL database size is large.
- Slower backup process
By utilizing the conventional spooling method, it could take a long time to back up the database and the speed is subject to various factors, including database size, network transfer speed, backup frequency, etc.
Forskellen på FULL Backup / Differential / Transaction log
Transaction Log backup. The information below gives you an overall idea of what each backup set
type is like.
To perform a Full backup, AhsayOBM requests the SQL server to generate a Volume Shadow Copy
Service (VSS) snapshot of the database. AhsayOBM will back up the VSS snapshot generated by the
SQL server directly. A Full backup is required in order to run Differential backups.
A Differential backup of the SQL server saves changes to the database that have occurred since the
last Full backup. To perform a Differential backup, AhsayOBM requests the SQL server to generate a
Differential backup file of the database since the last Full backup. At the back end, the SQL server
performs the following:
1. Generate a VSS snapshot of the database of the current state.
2. Compare the VSS snapshot just generated by the SQL server with the one generated from
the last Full backup in order to produce a Differential backup file.
3. The Differential backup file being sent to online-backup.dk Manager for backup. Using a Differential backup file to recover a database requires the restoration of only two data sets –
The last Full backup and the most recent Differential backup.
The disadvantage of using Differential backups is that it duplicates the backed up data in each backup
until a Full backup is performed. If there are many Differential backups taken between Full backups,
the storage space required may become large.
The SQL server does not allow a Differential backup to occur when there has been no previous Full
backup to establish the starting point.
Every SQL Server database has a transaction log that records all transactions and the database
modifications made by each transaction. The transaction log is a critical component of the database. If
there is a system failure, you will need that log to bring your database back to a consistent state.
If you have chosen to back up in ODBC mode, you can configure schedule backup to back up the
transaction log regularly at a time interval of your choice.
BACKUP LOG is not allowed while the recovery model is SIMPLE.
Din(e) SQL-database(r) er blevet oprettet som en simpel database og har ikke en transaktionslog.
Når transaction backup kører, mislykkes den, fordi der ikke er nogen log til backuppen. dette er normalt og ikke et problem.
Skift til “Full”
Login failed for user 'sa' / Database "XXX" not found
This happens when the user or password used for the SQL-server access is incorrect or the user does not have backup rights.
The MS SQL-backup works as follows:
- The backup task is started by the scheduler service, the backup process will start using the Windows-authentication provided.
- The process will then start a SQL client, which logs onto the SQL-server with the provided SQL-username and matching password.
- The SQL client will request the SQL-server to export the selected databases to the temporary directory.
- Here it will compare the export with the data present on the backup server and then upload the difference to the backup server.
In the MSSQL-backupset a SQL-username and password have to be provided, and optional a Windows log on as well for the temporary directory.
- Open the software
- Go to the backupset settings
- Choose the correct backupset
- Under the ‘General’ tab supply the Windows authentication and MSSQL server authentication
- Click ‘OK’
- Save the settings / Close the software
The backup rights of a user can be checked using the MSSQL Management Studio
- Start the MSSQL Management Studio
- Login using the same credentials used in the backup software
- In case this failes you will need to use the sa user to gain enough rights to make changes.
- This rights apply to each user and each database individually!
- Check the box in front of the db_backupoperator parameter for each database that should be included in the backup.
- Apply the changes
Alternativly you can also create a seperate SQL user using the Management Studio who has enough rights to make a backup.