Creating a SQL backup. Creating an automatic SQL database backup on the SQL Express Edition server. View information about backup and recovery events for database
Recove the database "Test _Recovery" to the state " t 4.».
We will proceed to restore the database from full backup "Full2_Test_Recovery.bak" using "SQL Server Management Studio " Click right-click Mice on the basis "Test _ Recovery. ", In the menu that appears, select"Tasks ", then" Restore ", then" Database ".
In the window that appears "Restore Database "In the" Sourse "section, select" Device ". Next "Add. ", Prescribe the path" \\\\vNIZ - TST - BKP 01. Test. Local \\ Backup _ SQL \\ Full 2_ Test _ Recovery. Bak ", click" OK ". In the section "Destination" choose Database "Test Recovery"
Click "OK"
The base will be successfully restored.
Consider the database recovery using TRANSACT-SQL.
Click right-click on the basis of "Test_Recovery", in the menu that appears, select "New Query":
In the window that appears, we enter:
Use master
Restore. Database Test_Recovery.
From. Disk \u003d "\\\\ VNIZ-TST-BKP01.Test.Local \\ Backup_sql \\ Full2_Test_Recovery.bak"
With. Replace
The base will be successfully restored.
In this example, we used the Replace parameter:
Recovery usually does not allow random database overwriting another database. If the database specified in the Restore statement already exists on the current server, and the GUID identifier for a given database differs from the GUID family identifier for the database recorded in the backup data set, then its recovery will not be executed.
The REPLACE parameter cancels several important checks that are usually performed by the recovery operation. The following checks are canceled.
- Checking for recovery over the existing backup database created for another database.When using the Replace parameter during recovery, you can write data over the existing database regardless of which databases are contained in the backup data set, even if the specified data name differs from recorded in the backup set. This can lead to a random overwriting over the database of another database.
- Checking the database recovery that uses the full recovery model or an incomplete log recovery model for which a backup copy of the final log fragment was not created, and the Stopat parameter was not applied.When using the REPLACE parameter, a loss of recorded data is possible, since the larded data recorded to the log have not yet been copied to the backup.
- Overwrite existing files.
After studying a variety of information from different sources, I decided to describe the process of setting reserve copy DB MS. SQL Server for full Recovery model, which model to use to solve you, but from myself I will add that if there are a large flow of information in your database (dozens, hundreds or thousands of documents are created in 1 hour), then the loss of information on the day of work will be simply unacceptable, in this case only The full model will ensure the safety of your data. Danna article is intended for novice system administrators and contains in my opinion Minimum set of actions for backup BD 1C. Installing \\ Setting the SQL server itself and deploy the database on it within the framework of this article.
All settings will be made using SQL Management Studio. First you need to create a backup device, you can not create, but in my opinion it is much more convenient and right. in snap SQL Management Studio -\u003e Server Objects-\u003e Backup Device. You need to specify the device name and the file in which the backup copies will be stored (better with the BAK extension), you can later see the content of the media, all backup copies will be listed there.
Now you can start setting up the service plan (Maintenance Plan). The service plan can be created immediately for all databases, but more convenient for each database to create your own service plan.
In our service plan there will be three boosted: 1 - backup database (full); 2 - backup bd (difference); 3 - Backup transaction log. Each subtente has its own execution schedule. Rapping everyone configures in its discretion, in my case full copy It is done once a week on Sunday, the difference copy every day except Sunday, Zht - the transaction log every hour. With such a reservation model, you can restore the desired database to any date and hour, and we save space on the hard disk. Full reservation is actually executed once a week, and during the week only changes.
Daily schedule setting. Weekly differs only by the "Sunday" tick and removed from Monday on Saturday 
Schedule for Zht. Red is allocated to save time during the day, it makes sense, for example, if users work with the database at a certain period if the operation of 24x7, then we leave to exploit.
The figure below shows the editor of the weekly underground, it consists of tasks that are performed in a given sequence. The sequence is set manually, and the green arrows mean that the next task will be executed only if the previous one is successful, and the blue that the task is completed with any completion of the previous task. You can add the tasks from the "Pantel of Elements" to the editor, the tasks can be added from the "Landpoint" panel when the editor is open.
Tasks. In each task you need to go and choose the database for which it will be performed and a number of other tunnels (if any). Consider what tasks contains a weekly sublated service plan.
1. "Check Database Integrity Task). The following task will be executed only if the database does not contain errors. (I will reserve the database with errors?)
2. "Restore Index" (Rebuild Index Task). Restore (rebuild) an index is necessary every day, because When working with indexes, they are very fragmented and more than 25% of SQL begin to "slow down" during fragmentation. This operation is quite resource, so it can be done at least once a week, and in daycredited replace it with a less resource-intensive task "Reorganization of the Index".
3. Update Statistics Task (Update Statistics Task). To optimize ... By the way, this task can be performed several times during the day, if your database is heavily loaded.
4. After updating the statistics, it is necessary to clear the procedural cache. To do this, drag to the editor "executing the T-SQL instruction" and in the "T-SQL instruction:" field. Write the procedure DBCC FreeProccache.. But you need to consider that this procedure clears the cache from all the database, and we updated the statistics on one! How to Clean Procedural Cache for a specific database, read. Briefly: DBCC FlushProcindB (ID_BD)
5. "Backup database" (Back Up Database Task). In this task, we indicate which database we reserve, the backup type (for the weekly underground is complete, for the daytime - the difference, for the hourly - transaction log.) We set the switch to "Create a backup copy of databases in one or more files" and add previously Created backup device. In this case, all copies are saved in one file, which indicated when creating backup devices, if the switch is left to "Create a backup file for each database", then on each backup will be created separate file And on the full and on the difference and on it, which is very uncomfortable when restoring, but it is convenient when stored. Do not forget to seek what you need to compress backup copies!
6. "Clearing Log" Clears the records created when performing tasks. You can also enable the "Clearing after maintenance" task and configure it to delete text logs or outdated backups.
Podfelny for backing up, consists of one "backup database backup" task. It is more convenient for me to save it not to a backup device, but to a separate file that you want to specify in the task setting.
2. View information about backup and recovery events for database
In order to find out when you create backup copies of a specific database, as well as restore the database from the backup, you can use the standard report " "(Backup and Restore Events). To form this report, you need to click on the Object Browser (Server Oblects) by right-clicking on the appropriate database, context menu choose " Reports"(Reports) -" Standard report"(STANDART REPORTS) -" Backup and Recovery Events"(Backup and Restore Events).
The report formed contains the following data:
- Average time spent on backup operations (Average Time Taken for Backup Operations)
- Successful Backup Operations (SaccessFul Backup Operations)
- Backup Operation Errors (Backup Operation Errors)
- Successful Restore Operations Operations
To view this information, you must disclose the corresponding grouping in the report.
Will this article help you?
"Who owns information - he owns the world" - Mayer Ambrel Rothschild
The most valuable essence in any business is information. The loss of information can lead to unpredictable consequences, mainly financially. Therefore, one of the main tasks of IT specialists is to back up all IT infrastructure. This applies to MS SQL Server databases.
In order to ensure the safety of information in the databases used, as well as reduce the time to restore performance, it is necessary to perform periodic backup of SQL servers.
We will analyze on the simplest example: you need to configure the database backup to a separate disk.
Decision:
- Open Microsoft SQL. Server Management Studio.. In the navigation menu on the right, open the tab "Control". We see the tab "Service Plans". Press the right mouse button -\u003e "Create Service Plan" And give the name to our plan (Fig. 1):
Fig.1 Creating a new service plan.
2. On the element panel add a task "Backup database" (Fig.2):
Fig.2 Adding a "backup database backup" task.
3. On the created task, press the right mouse button -\u003e "Change" (Fig.3):
4. In the Task Properties window, select the backup type (in my case full), select the desired database (I have KA_CONS), a directory for backups, the ability to check backup copies for integrity and compression options (Fig.4-6):
Fig.4 Type Bacup - Full.
Fig.5 Select database for backup.
Fig.6 Determining a catalog for backups, checking integrity and degree of compression.
5. On the service plan setting panel on the right. Click on the button "Schedule" (Fig. 7):
6. Configure the Schedule we need and click "OK" (Fig. 8):
Fig.8 Setting backup schedules.
7. We save our service plan (Fig. 9):
Fig. 9 Saving a service plan.
The full backup of the schedule database is configured.
It is recommended to configure regular backup database (in case of hardware or software failures), It is best to maintain backup copies over the past few days, for example, seven (for the last week).
To do this, you can use or embedded in SQL Server job scheduler - SQL Server Agent (in free version Not included), or the standard Windows Planner in combination with the SQLCMD.exe utility, which allows you to perform requests to SQL Server from command line. The planner needs to create at least seven tasks (one for every day of the week), each of which will (once a week) replace one of the seven files containing the corresponding backup of the database.
In addition, backup files are recommended not only on the hard disk of the computer, where SQL Server is installed, but also to duplicate them on the tape or hDD another computer on the network. To do this, you can use either a special software that allows you to backup copies of the entire disk, or using the same scheduler to copy files to a tape or another computer (second step).
Using the Windows Planner (for the free version)
To create a task in " Windows scheduler"It is necessary:
Start the Notepad program (Start-\u003e All Programs-\u003e Standard-\u003e Notepad) and enter the following two lines, after which you save them as a command file (* .bat):
SQLCMD -S (Local) -E -Q "Backup Database Altasvhdb to Disk \u003d" D: \\ Backup \\ Altasvhdb_monday.bak "With init, Noformat, Skip, NounLoad"
XCOPY D: \\ Backup \\ altasvhdb_monday.bak \\\\ Backup_Server \\ Folder \\ *. * / Y
where "(Local)"- Server Name (In the case of setting a named instance of SQL Server, you must specify the name fully: "object_name \\ sqlexpress"), "AltasvHDB" - database name, "D: \\ backup \\ altasvhdb_monday.bak" - the file name to create a backup copy in it (will vary by days of the week), "Backup_Server" - the name of the computer to which additional copy will be performed, "Folder" - Folder on this computer (access to it should be provided).
Run the task scheduling wizard (Control Panel-\u003e Assigned Tasks-\u003e Add Task) and click the Next button:
Click the "Overview" button and specify the path to the command file (* .bat) created in step a):
Specify the name for the task, select the launch option "Weekly" and click the Next button:
Put a tick near the desired day of the week, and in the "Startup Time" field, specify the time when the backup process should be launched (usually done at night), then click the Next button:
Enter the username and password (twice) account account, on behalf of which the task will be executed, and click the Next button:
Attention! For the task successfully completed, you must provide the account indicated here (Domain or local computer) Record rights to the aforementioned folder «\\\\ Backup_Server \\ Folder», as well as configure access to the SQL Server itself.
Press the "Finish" button
Note. To check the performance of the created task, you must in the list of tasks (Control Panel-\u003e Assigned Jobs) Press the Right-click on the task you are interested in and select "Run" in the context menu, then make sure that the backup file of the database has been successfully created on the way that ways that were indicated in step a).
With the help of "SQL Server Agent" (not included in the free version)
To create a task in "SQL Server Agent" it is necessary:
Run the SQL Server Management Studio utility and connect to the server under the administrator account.
On the left side of the window, you right-click on the "Objects of the Server / Requirement Device" and in the context menu, select Create Backup Device:
In the "Device Name" field, enter a name that will be associated with a backup file of the database, if necessary, change the path in the File field and click "OK":
On the left side of the window, right-click on the "SQL Server / Job Agent" section and select "Create Task" in the context menu:
In the "Name" field, enter the name of the task:
On the "Steps" page, click the "Create" button:
In the window that appears, enter a name in the "Step Name" field, check that the "TRANSACT-SQL (T-SQL) script (T-SQL)" field is selected, and enter the string in the command field:
Backup Database Altasvhdb to Altasvhdb_Monday with Init, Noformat, Skip, NounLoad
where "AltasvHDB" - database name, "Altasvhdb_monday" - the name of the backup device created in step c) (will vary by days of the week):
In the previous window, click the "OK" button, as a result, a string should appear on the Steps page:
To the database backup file immediately copied to another computer on the network, you must repeat the F) - h paragraphs, selecting the value in the "Type" field. Operating system (CmDexec) ", and in the" Command "field, specifying the string:
XCOPY D: \\ MSSQL \\ Backup \\ altasvhdb_monday.bak \\\\ Backup_Server \\ Folder \\ *. * / Y
where "D: \\ Mssql \\ backup \\ altasvhdb_monday.bak" - the path specified in step c) (will vary by days of the week), "Backup_Server" - the name of the computer to which copy will be performed, "Folder" - Folder on this computer (access must be provided to it):
Note. To copy the file successfully, you must run "SQL Server Agent" under the Windows domain account, for which the recording rights are provided to the above folder (see also "SQL2005_INSTALLATION.DOC" or "SQL2008_Installation.doc"), and also configured access to SQL itself Server (see "Setting up access rights to the database", enable this account It is necessary to "SysAdmin" on the "Server Roles" page, and do not do anything on the "Mapping of Users" and "Protected Objects" pages.
On the "Schedules" page, click the "Create" button:
Enter the name in the "Name" field, check that the "Repeating Task" value is selected in the "Type of Schedule" field, and in the field "executed" - "Weekly". Put a tick near the desired day of the week (the others to remove), and in the "One-time task" field, specify the time when the backup process should be launched (is usually done at night):
In the previous window, click the "OK" button, as a result, a string should appear on the Schedule page:
Press the "OK" button.
Note. To check the performance of the created task, it is necessary in the "SQL Server / Task Agent" section on the Right mouse button on the task you are interested in and select the "Run Task in the Step" item in the context menu, select the first step in this task and click "OK". Next appears the Displays the task execution step. If the task is completed with an error, then detailed description Errors can see calling the "View Log" item of the same context menu.