Sql server express backup. Creating an automatic backup of the SQL database on the SQL Express Edition server. Restoring a database from a backup
After studying a lot of information from different sources, I decided to describe the process of setting up a MS SQL Server database backup for full recovery model, which model to use is up to you, but I will add that if there is a large flow of information in your database (for example, tens, hundreds or thousands of documents are created in 1 hour), then the loss of information during a day of work will be simply unacceptable, in this case only the complete model will ensure the safety of your data. This article is intended for novice system administrators and contains in my opinion the minimum set of actions for backing up a 1C database. Installing\configuring the SQL server itself and deploying a database on it is not within the scope of this article.
We will make all settings using SQL Management Studio. First you need to create a Backup Device, you don’t have to create it, but in my opinion it is much more convenient and correct. in a snap SQL Management Studio -> Server Objects -> Backup Devices. You need to specify the name of the device and the file in which the backups will be stored (preferably with the BAK extension), then you can view the contents of the media, all backups will be listed there.
Now you can start setting up the Maintenance Plan. A Maintenance Plan can be created for all databases at once, but it is more convenient to create your own maintenance plan for each database.
Our Service Plan will have three subplans: 1 - database backup (Full); 2 - database backup (Difference); 3 - Backup the Transaction Log. Each subplan has its own execution schedule. Everyone sets up the schedule according to their own discretion, but in my case, a full copy is done once a week on Sunday, a differential copy every day except Sunday, and a transaction log every hour. With this backup model, you can restore the desired database to any date and time, and we save space on your hard drive because A full backup is actually performed once a week, and only changes are made during the week.
Setting up a daily schedule. Weekly differs only in the checkbox "Sunday" and unchecked from "Monday" to "Saturday" 
Schedule for railway transport. The saving time during the day is highlighted in red, it makes sense, for example, if users work with the database during a certain period, if the operating mode is 24x7, then we leave it at default.
The figure below shows the weekly subplan editor; it consists of tasks that are performed in a given sequence. The sequence is set manually, and green arrows mean that the next task will be completed only if the previous task is completed successfully, and blue arrows mean that the task will be completed whenever the previous task is completed. In the maintenance subplan editor, tasks can be added from the "Elements Panel", which is located in the upper left corner when the editor is open.
Tasks. You need to go into each task and select the database for which it will be executed and a number of other settings (if any). Let's look at what tasks the weekly subplan of our maintenance plan contains.
1. “Check Database Integrity Task”. The following task will only be executed if the database does not contain errors. (Should we back up the database with errors?)
2. "Rebuild Index Task". It is necessary to restore (Rebuild) the index every day, because... When working with indexes, they become highly fragmented, and when fragmentation exceeds 25%, SQL begins to noticeably slow down. This operation is quite resource-intensive, so it can be done at least once a week, and daytime subplan to replace it with the less resource-intensive task "Index Reorganization".
3. “Update Statistics Task”. For optimization... By the way, this task can be performed several times during the day if your database is heavily loaded.
4. After updating the statistics, you MUST clear the procedural cache. To do this, drag the “Execute T-SQL statement” task into the editor and write a procedure in the “T-SQL statement:” field DBCC FREEPROCCACHE. But you need to take into account that this procedure clears the cache of ALL databases, and we updated the statistics one at a time! How to clear the procedural cache for a specific database, read. In short: DBCC FLUSHPROCINDB(DB_ID)
5. "DB Backup" (Back Up Database Task). In this task, we indicate which database we are backing up, the type of backup (For a weekly subplan - Full, for a daily subplan - Differential, for an hourly - Transaction log.) We put the switch in the position "Create a backup copy of databases in one or more files" and add it earlier created backup device. In this case, ALL copies are saved in one file, which was specified when creating backup device, if the switch is left in “Create a backup file for each database”, then for each backup a separate file will be created for Full, Differential and VT, which is very inconvenient when restoring, but convenient when storing. Don't forget to indicate that you need to compress backups!
6. "Clear Log" Clears records created when executing tasks. You can also enable the "Post-Maintenance Cleanup" task and configure it to delete text logs or outdated backups.
The subplan for VT backup consists of one task “Database backup”. For me, it is more convenient to save the VT not to the Backup Device, but to a separate file, which must be specified in the task settings.
The extensive functionality of Bacula Enterprise Edition, among other things, allows you to quickly and easily create database backups for . For example, we are talking about a tool with which you can backup MS SQL Server. The user can make a backup of MS SQL by creating large-volume backups of specific MS SQL databases used by the Windows platform, at lower costs for third-party software, with the ability to restore data to a certain point in time (PITR recovery) to a network and local drive.
The Bacula Systems script for creating MS SQL Server backups is characterized by extreme efficiency, achieved through the implementation of a modern, highly reliable architecture. Moreover, the software allows you to make a backup of MS SQL Server and use a variety of options for creating MS SQL backups.
The MS SQL Bacula Systems backup script operates independently of VSS. This means that the MS SQL backup tool does not use VSS snapshots to create backups. Therefore, the user can set the following value “Enable VSS = no” in Bacula FileSet. Effective creation of MS SQL Server backups and their restoration using this solution is achieved through the use of the Microsoft API for SQL Server. This allows Bacula Systems to support the security mechanisms and all types of authentication implemented in Microsoft SQL Server.
MS SQL transaction log backup and MS SQL point-in-time recovery: Bacula Enterprise Edition software allows you to recover MS SQL data blocks or specific settings to a specific point in time. With the implementation of full and bulk-logged recovery models, you can recover MS SQL using PITR recovery, or use LSN to restore the system to a specific state. You can restore a specific state of an MS SQL database at any specific point in time, down to the second. In the case of a MS SQL transaction log backup, when restoring, the database state will be restored from various selected backups.
Features at a Glance automatic backup and recovery of MS SQL with Bacula Enterprise
Bacula Systems has created a MS SQL Server backup plugin for use with Bacula Enterprise Edition. MS SQL Server backup with Bacula has the following features:
- Supports full and differential MS SQL backups
- MS SQL incremental backup support
- MS SQL backup to network and local drive
- Scheduled MS SQL backup
- Creating backups at the MS SQL Server database level
- Ability to include/exclude databases from the backup creation procedure
- Support for creating read-only database backups
- Restoring MS SQL backups to disk
- Sending a backup stream directly to the Storage Daemon
- MS SQL point in time recovery
Review and configuration of backup MS SQL 2008, 2008 R2, 2012 and 2014
This document provides solutions for Bacula Enterprise Edition 8.4 and later that are not supported by earlier versions of the software. MS SQL database backup has been tested and is supported by MS SQL 2003 R2, MS SQL 2008 R2, MS SQL 2012, MS SQL 2005, MS SQL 2008, MS SQL 2014. MS SQL backup from Bacula can work with SQL Express.
MS SQL Backup Glossary 2008, 2008 R2, 2012 and 2014
- MS SQL stands for Microsoft SQL Server.
- Transaction log. Any MS SQL Server database has a transaction log, which records all transactions and database modifications performed during such transactions. The transaction log is an important element of the database. In the event of a system failure, the transaction log may be required to restore the database to a working state. More information can be found at https://msdn.microsoft.com/en-us/library/ms190925.aspx.
- Differential backup of MS SQL Server database. Differential backup is based on the last full one. During a differential backup, only the data that has changed since the last full backup was created is captured. More information can be found at https://msdn.microsoft.com/en-us/library/ms175526.aspx.
- Full backup of MS SQL Server database. During a full database backup, a backup copy of the entire database is created. The backup includes part of the transaction log for the purpose of restoring the complete database from the backup. Full database backups contain the database at the time the backup was completed. More information can be found at https://msdn.microsoft.com/en-us/library/ms186289.aspx.
- Backup “copy only” (CopyOnly). Copy-only backups are MS SQL backups that are independent of the normal flow of traditional SQL Server backups. Sometimes it is useful to create backups for specific needs without affecting the overall database backup and recovery process. More information can be found at https://msdn.microsoft.com/en-us/library/ms191495.aspx.
- VDI(Virtual Device Interface) is a Microsoft technology that allows you to create named pipe between programs.
- LSN Each entry in the MS SQL Server transaction log is identified by a unique transaction serial number (LSN). More detailed information can be found at https://technet.microsoft.com/en-us/library/ms190411%28v=sql.105%29.aspx.
Backup MS SQL Server 2008, 2008 R2, 2012 and 2014
Full backup of MS SQL Server 2008, 2008 R2, 2012 and 2014 databases
During a full backup of an MS SQL database, the database files and transaction log are saved, which allows you to fully protect the MS SQL database in case of media failure. If one or more files are damaged, restoring the MS SQL database from a backup will allow you to restore all completed transactions. All transactions that were in progress will also be rolled back. In this mode, backups of the master and mbdb databases are created.
Differential backup of MS SQL Server 2008, 2008 R2, 2012 and 2014 databases
Differential backup of MS SQL Server database is based on the latest full backup of MS SQL database. When creating a differential MS SQL backup, only the data that has been changed since the last full MS SQL backup was created is captured. For the MS SQL differential backup function, the sequence of backups is extremely important. If for some reason the full backup referenced by MS SQL is not available, differential backups of the MS SQL Server database cannot be used. Bacula's MS SQL Backup uses specific techniques to solve this problem. Therefore, if difficulties arise, the status of a differential database backup can be automatically upgraded to a full backup.
Transaction log backup of MS SQL 2008, 2008 R2, 2012 and 2014
Setting up MS SQL backup and database configuration
Restoring a MS SQL database from a backup
You can use all the standard methods to start the procedure for restoring a MS SQL database from a backup. However, you must ensure that in case of restoring differential data, the full previous backup of the MS SQL database will also be restored. In this case, recovery occurs automatically if you run it in the console bconsole using recovery options 5 or 12. In the generated file structure, you need to mark the recovery of full databases or DB instances.
Options for restoring a MS SQL database from a backup
Bacula Enterprise Edition software allows users to use multiple MS SQL recovery options and apply a variety of database rollback methods. The most commonly used recovery options are described below:
- Where parameter: In the case of Bacula Enterprise Edition, this parameter allows the administrator to restore the database to a specific location.
- Replace parameter: Used to define how Bacula should behave with the current database when restored. Bacula's MS SQL backup also allows you to use several more options when restoring, such as:
- Instance: Since MS SQL uses multiple instances, MS SQL database backup from Bacula allows you to choose which instance to restore. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring. By default, an instance named “MSSQLSERVER” is used.
- Database. This option specifies the name of the database to restore and it uses the value specified at the time the database was created. This parameter is optional. By default, SQL Server database backups use the Where parameter to determine the name of the new database. If both the Where and Database parameters are assigned a valid database name, then the Database parameter will be used.
- User. The username used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring.
- Password. Password used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring.
- Domain. The domain used to connect to the MS SQL database instance. This parameter is optional, and if it is not specified, the value specified when creating the backup will be used when restoring.
- Recovery. The parameter allows you to determine whether the database will be rolled back to its previous state during recovery or not. By default, when restoring a database, it will roll back to the previous state.
- Stop_before_mark. Condition WITH STOPBEFOREMARK =
Used to indicate that the transaction log entry immediately preceding the flag is the restore point. The recovery point can be a date and time, an LSN, or a mark_name flag. - Stop_at_mark. Condition WITH STOPATMARK =
Used to indicate that the marked transaction is a recovery point. STOPATMARK moves forward to the flag and replays the marked transaction. The recovery point can be a date and time, an LSN, or a mark_name flag. - Stop_at=
. Condition WITH STOPAT = is used to indicate that the restore point is date/time. - Restrict_user. The WITH RESTRICT_USER clause is used to restrict access to the restored database. The default is no.
Restoring MS SQL to a point in time can be performed directly from the MS SQL backup plugin. You can also restore files locally and perform operations from the Microsoft SQL Server Management Console to gain more functionality.
LSN
The LSN number of the log entry at which a specific backup and recovery event occurred can be viewed in one of the following ways:
- When displaying a description of tasks for creating backups using Bacula software
- In the log file name
- In table msdb.backupset
- In the msdb.backupfile table
When performing a task to create a backup of an MS SQL database, the following information about LSN numbers will be displayed when displaying the task description:
Number First LSN corresponds to the last LSN number of the last transaction log backup. Such a backup can be the very first full backup or the last backup (incremental).
Number Last LSN matches the last transaction recorded in the journal.
In the case of a transaction log backup (incremental), the name of the file associated with this database in the task for creating an incremental backup will look like this:
The number in the name, in our case 42000162001, corresponds to the last LSN number of the previous task (to create a full or incremental backup).
Figure 2: First LSN, Last LSN, and LSNs in File Names
As shown in the example in Figure 2, if the administrator needs to restore the MS SQL database to a state corresponding to LSN number 14, the following steps can be performed:
- In the database recovery menu, use option 5
- Select the latest full backup file “data.bak” (LSN: 10)
- Select incremental backup “log-10.trn”
Or, if the latest full backup of MS SQL Server is not available, but the previous full backup is available, then:
- Use restore option 3, select appropriate jobids values
- Select the database directory “/@mssql/db29187”
- Select the full backup file “data.bak” (LSN: 2)
- Select incremental backups “log-2.trn”, “log-3.trn”, “log-10.trn”
- Set the stop_at_mark parameter to “lsn:14”
- Run the task to restore the backup
MS SQL recovery scripts
| Description | Where | Database | Example |
| Recover files to disk | Path | where=c:/tmp | |
| Restore original database | where=/ | ||
| Restore with new name | Name | where=newdb | |
| Restore with new name | Name | database=newdb | |
| Recover with new name and move files | Name |
Table 1: MS SQL recovery scenarios
2.3.1 Restoring a MS SQL database with the original name
To restore the database with the original name, the option Where must not be specified (empty value), or the value “/” must be specified, and the parameter Replace must be assigned a value Always, or you must first delete the source database.
Restoring a MS SQL backup with a new name
To restore a MS SQL database backup with a new name, you may first need to move the database files to disk. It all depends on whether the original database still exists.
If the source database is no longer available, then the parameter where, or the “Plugin Options” field may contain the name of the new database. MS SQL Backup from Bacula will automatically create the database with a new name.
If the original database is still needed, the where parameter will be used to move the files to disk, and you will need to name the new database using the Plugin Options menu. In the recovery tree, you must select the layout.dat file.
Using My Catalog
Run the MS SQL recovery task:
Using the My Catalog, run the MS SQL database recovery task:
Recover MS SQL to local disk
If you specify where=c:/path/, the files will be restored to the local disk, and the MS SQL database administrator will be able to use the TSQL procedural extension for the Microsoft SQL Server Management Console to restore the database. The SQL commands required to restore the database are listed in the description Job output as shown in the picture below.
2. View information about backup and restore events for a database
In order to find out when backup copies of a specific database were created, as well as when the database was restored from a backup copy, you can use the standard report “ » (Backup and Restore Events). To generate this report, you need to right-click on the corresponding database in the Object Browser (Server Oblects) and select “ Reports" (Reports) - " Standard report" (Standard Reports) - " Backup and restore events» (Backup and Restore Events).
The generated report contains the following data:
- Average Time Taken For Backup Operations
- Successful Backup Operations
- Backup Operation Errors
- Saccessful Restore Operations
To view this information, you need to expand the corresponding grouping in the report.
Did this article help you?
Script for dynamic backup of all databases on the server. Then create a batch file as per the article. It is useful to create two batch files: one for the full backup and one for the backup. Then create two tasks in the task scheduler, one for full and one for diff.
-- // Copyright © Microsoft Corporation. All Rights Reserved. -- // This code released under the terms of the -- // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) USE GO /****** Object: StoredProcedure. ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================= ============ -- Author: Microsoft -- Create date: 2010-02-06 -- Description: Backup Databases for SQLExpress -- Parameter1: databaseName -- Parameter2: backupType F=full, D =differential, L=log -- Parameter3: backup file location -- ==================================== ========== CREATE PROCEDURE . BAK" ELSE IF @backupType = "L" SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, "[",""),"]","")+ "_LOG_"+ @dateTime+ ".TRN" -- Provide the backup a name for storing in the media IF @backupType = "F" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" full backup for "+ @dateTime IF @backupType = "D" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" differential backup for "+ @dateTime IF @backupType = "L" SET @BackupName = REPLACE(REPLACE(@DBNAME,"[",""),"]","") +" log backup for "+ @dateTime -- Generate the dynamic SQL command to be executed IF @ backupType = "F" BEGIN SET @sqlCommand = "BACKUP DATABASE " +@DBNAME+ " TO DISK = """+@BackupFile+ """ WITH INIT, NAME= """ +@BackupName+""", NOSKIP, NOFORMAT" END IF @backupType = "D" BEGIN SET @sqlCommand = "BACKUP DATABASE " +@DBNAME+ " TO DISK = """+@BackupFile+ """ WITH DIFFERENTIAL, INIT, NAME= """ +@BackupName+""", NOSKIP , NOFORMAT" END IF @backupType = "L" BEGIN SET @sqlCommand = "BACKUP LOG " +@DBNAME+ " TO DISK = """+@BackupFile+ """ WITH INIT, NAME= """ +@BackupName+""" , NOSKIP, NOFORMAT" END -- Execute the generated SQL command EXEC(@sqlCommand) -- Goto the next database SELECT @Loop = min(ID) FROM @DBs where ID>@Loop END
And the batch file might look like this:
Sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation="c:\Dropbox\backup\DB\", @backupType="F"" >> c:\Dropbox\backup\DB\full.log 2>&1
Sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation="c:\Dropbox\backup\DB\", @backupType="D"" >> c:\Dropbox\backup\DB\diff.log 2>&1
The advantage of this method is that you don't have to change anything if you add a new database or remove a database, you don't even have to list the databases in the script. JohnB's answer is better/easier for a single database server, this approach is more suitable for multi database servers.
A differential backup is based on the most recent previous full backup of the data. A differential backup only saves changes that have been made since the last full backup.Recommendations:
- Use differential database copies if creating a full database copy takes a long period of time
- Periodically make a full copy of the database to reduce the volume of differential copies created.
- After creating a full copy of the database, all previous differential copies lose their relevance.
Let me give you a small practical example of why we started using a difference copy. Over time, our client’s database grew to such a size that creating a full backup took 8 hours, several more months, and perhaps this operation would not have time to be completed by the beginning of the working day. After switching to differential backup, we reduced the time from 8 hours to 2-4 minutes (depending on the day of the week). Once a week we made a full copy of the database.
Example SQL for creating a differential backup copy of a database with checking the copy upon completion (different from a full copy with the flag DIFFERENTIAL you should use it instead NOFORMAT).
Declare @pathBackup as varchar(55) set @pathBackup = N"C:\Backup\[DB file name]_" + REPLACE(convert(varchar,GETDATE(), 104),".","_") + " .bak" BACKUP DATABASE [Database Name] TO DISK = @pathBackup WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N"Full Database Backup", SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM GO declare @backupSetId as int declare @pathBackup as varchar(55) set @pathBackup = N"C:\Backup\[DB file name]_" + REPLACE(convert(varchar,GETDATE(), 104),".","_") + " .bak" select @backupSetId = position from msdb..backupset where database_name=N"[Database name]" and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N"[Database name]") if @backupSetId is null begin raiserror(N"Verification error. Backup information for database '[Database Name]' was not found.", 16, 1) end RESTORE VERIFYONLY FROM DISK = @pathBackup WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO
3.System databases
In addition to the main database and its associated files, I strongly recommend making copies of the system databases. Let's start by looking at what databases exist in MS SQL. There are only 5 of them:I chose to back up only 2 system databases:
- msdb - because configured tasks and others are stored there
- master – all configured SQL Server settings are stored.
4. Backup plan
Based on the above, we will draw up our data backup plan. It may differ from what you need, it all depends on the requirements for database recovery. When I prepared the plan, I had to take into account that it was necessary to recover the data as much as possible and the loss of data was no more than one hour.We will make the following backups:
- A full copy of the main database, more than once a week is not necessary
- Differential copy of the main database, every day
- Copies of the main database transaction log, every hour
- Copy of the master system database, once a week
- Copy of the msdb system database, once a week
| Day of the week |
Time |
Actions |
Frequency |
Description |
| Monday Friday |
From 8-00 to 21-00 |
Backups Transaction log |
Each hour |
After performing a database backup, the transaction log is compressed and truncated |
| Saturday Sunday |
From 8-00 to 18-00 |
|||
| Monday Sunday |
22-00 |
Differential copy of the main database |
1 per day |
After a successful differential copy runs, all old transaction log copies are deleted |
| Saturday |
12-00 |
Database check |
1 per day |
Checking the database for integrity. |
| Saturday |
18-00 |
Creating a full copy of the database |
1 per day |
Upon completion of this operation, a notification is sent by email. If the backup was created successfully, it is deleted
|
| Monday Sunday |
23-30 |
Creating a copy of the master system database |
1 per day |
|
| Sunday |
12-30 |
Creating a copy of the msdb system database |
1 time per month |
Only the last instance of the database is always stored |
- Use the BACKUP WITH CHECKSUM option
to make sure everything went well. The disadvantage of this solution is that for large databases, checking the checksum can seriously load the system. - Do not back up files to the same physical disk that stores the database or transaction log.
- If you use MS SQL 2008 or higher, I recommend that you use SQL backup compression. The following code will enable compression by default: USE master; GO EXEC sp_configure ‘backup compression default’, “1”; RECONFIGURE WITH OVERRIDE;
- Keep backups for several days in case one of them gets corrupted - an old backup is better than no backup.
- Use DBCC CHECKDB to check each database before copying, this will promptly alert you to impending problems. DBCC CHECKDB("Database Name") WITH NO_INFOMSGS, ALL_ERRORMSGS; Note: In practice, we used this check only before performing a full backup.
- Periodically update statistics and reorganize database indexes
Using the application
A few nuances about the application:- All texts and queries in the code are included in resources, it was easier for me
- When you enter connection parameters and other settings, they are saved to a file. For Express and Standard, different files are used (dbStandart, udExpress) and the UserData class is stored in them
- Some operations may require administrator rights
- At the moment, the connection to the database under a domain account is not working
- The program does not have a super beautiful interface
1. Setting up admin notification
I was too lazy to log into the server every time and check whether the task worked or some error occurred. And I wanted to be able to receive other notifications, not just about task completion.DatabaseMail MS SQL is used for this purpose (for Standard version and higher)
In my application I made a special section to automate this task
When clicked, a form will appear for filling out the information necessary to create a mailing profile:
The application is automatically configured for standard SMTP port 25 for the address from which letters are sent. If necessary, it can be changed in the sysmail_add_account_sp procedure
A user and password are required in case the mail service has authentication configured.
The operator name in the system is indicated so that we can properly create a profile in DatabaseMail. Write any name that will be clear to you. Below is an example of a completed form.
- MS SQL system parameters change.
- DatabaseMail Profile is created
- Activated in SQL Agente profile
- DatabaseMail Account is created
- Adding DatabaseMail Account to Database Mail Profile
- DatabaseMail Operator is created
2.Additional notices for the administrator
The program provides 2 tasks applied to the database:- checking the integrity of the database. The standard procedure DBCC CHECKDB was used to check the database.
- informing about free space in file groups.
- The second task was implemented using a query to the dbo.sysfiles system table
- Here is an example of this query that was executed against the database:
The response from the server comes to the administrator's email in the form of html markup. This syntax is possible thanks to the following standard MS SQL FOR XML function.
Also, while I was looking for how to convert the returned result of queries into html text, I came across the following page, where a person created an entire procedure for these purposes
You can configure these operations using the corresponding item in the program menu:
A window will appear to indicate the mailbox to which the html text of the report should be sent:
3. Solving problems when setting up DatabaseMail
In MS SQL 2008 I encountered a problem when setting up SQL Server Agent. The symptoms are as follows: after configuration, it is impossible to start SQL Agent. This is mainly solved by installing update on the SQL server.If these updates do not help, you need to download the fix. It can be found on this site; I can’t provide the final link right now; in order to get to the fix package, you will need to answer a number of questions.
If there are problems with the DatabaseMail module. After setting up this module using the application, you need to go to SQL Agent and view the event log. If there are errors “unable to connect to the mailbox”. This means there is a problem, even if the letter is sent through verification.
This can be corrected by the following manipulations:
- Management Studio - SQL Server Agent - Properties.
- Alert System
- Uncheck Enable mail profile
- Click OK
- Login again and check the box
- Restart SQL Server Agent.
4.Set up backup using the SQL Standard application:
Select the Standard version. Setting up notifications. (see section, notification settings):
We connect to the database, filling in the connection data and specifying the database for which the Job will be used:
Select the backup setting:
Specify the paths for saving database copies. If the specified folders do not exist, the program will try to create them (the appropriate rights are required).
Click save and the corresponding tasks are configured in the database. It is advisable to configure different folders for each backup, because... When deleting, all files with the bak extension will be deleted. (cm. section deleting database copies)
5.Set up backup using the SQL Express application:
Since SQL Express does not have a SQL Agent, the task of automating backups had to be solved in a different way. A bat file is created in the folder specified by the user, which describes the SQL query responsible for creating the backup copy. If necessary, you can edit it directly. In addition to this, the standard Windows scheduler should work; it creates a task that will run once a day at the specified time.To do this, launch the application. Select MS SQL Express:
A form appears for filling out the parameters:
We indicate where our copy will be saved, as well as where the bat file will be located to create a copy of the database (there is no need to specify the file name, it will be specified automatically). Next, we specify the connection settings and the time when the task needs to be launched.
The only disadvantage of this approach is that the password for connecting to the database has to be stored in clear text.
6.Deleting tasks from the database.
If you need to delete all tasks from the database (for example, you wanted to change the paths to save the database). To do this, use the corresponding item in the program menu. All tasks with a certain starting prefix (in my case King) will be deleted from SQL Agent:
7.Deleting database copies
In some tasks, old database copies are configured to be deleted. To do this I use the master.dbo.xp_delete_file procedure. Usage example: Will delete all files with the bak extension from the specified folder whose creation date is more than 14 days old.EXECUTE master.dbo.xp_delete_file 0,"E:\backups",N"bak",dateadd(d,-14,getdate()),0;
And here is another more detailed example and information about what parameters this function takes.
How to restore backups
Due to lack of time, the recovery module has not yet been implemented, perhaps in the future I will add it, but for now I will simply briefly describe how the database can be restored.Using SQL script. To restore a database, use the RESTORE command.
If you just need to restore the database from a full copy, then just run the following script:
RESTORE DATABASE [Database name] FROM DISK = "Z:\SQLServerBackups\back.bak" WITH REPLACE
If you need to restore sequentially a full copy, differential copies and transaction logs, then you need to write the following SQL script.
RESTORE DATABASE TEST_DB – restoring a full copy FROM test_db_full WITH NORECOVERY; GO RESTORE DATABASE TEST_DB – restoring the difference copy FROM test_db_diff WITH FILE = 1, NORECOVERY; GO RESTORE LOG TEST_DB – restore transaction log No. 1 FROM test_db_tran_1 WITH FILE = 1, WITH NORECOVERY; GO RESTORE LOG TEST_DB – restoring transaction log No. 2 FROM test_db_tran_2 WITH FILE = 1, WITH NORECOVERY; GO RESTORE DATABASE TEST_DB WITH RECOVERY; GO
You can also use SSMS to restore the database.
Tags:
- Backup MS SQL
- MS SQL backup
