SQL. Backup setup
“He who owns information owns the world” - Mayer Amschel Rothschild
The most valuable asset in any business is information. Loss of information can lead to unpredictable consequences, mainly financial. Therefore, one of the main tasks of IT specialists is backup of the entire IT infrastructure. This also applies to MS SQL Server databases.
In order to ensure the safety of information in the databases used, as well as to reduce the time to restore functionality, it is necessary to perform periodic backups of SQL servers.
Let's look at a simple example: you need to configure database backup to a separate disk.
Solution:
- Opening Microsoft SQL Server Management Studio. In the navigation menu on the right, open the tab "Control". There we see a tab "Service Plans". Right click -> "Create a service plan" and give a name to our plan (Fig. 1):
Fig.1 Creating a new service plan.
2. Add a task on the toolbar "Database Backup"(Fig.2):
Fig.2 Adding the "Database backup" task.
3. On the created task, right-click -> "Change"(Fig.3):
4. In the task properties window, select the type of backup (in my case, full), select the desired database (I have ka_cons), the directory for backups, the ability to check backups for integrity and compression options for them (Fig. 4-6):
Fig.4 Backup type - full.
Fig.5 Selecting a database for backup.
Fig.6 Defining the directory for backups, checking integrity and compression ratio.
5. On the service plan settings panel on the right. press the button "Schedule"(Fig.7):
6. Set up the schedule we need and click "OK"(Fig.8):
Fig.8 Setting up a backup schedule.
7. Save our service plan (Fig. 9):
Fig.9 Saving a maintenance plan.
Scheduled full database backup is configured.
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
- Successful Restore Operations
To view this information, you need to expand the corresponding grouping in the report.
Did this article help you?
Let's restore the “Test _Recovery” database to the “ t 4».
Let's start restoring the database from a full backup "Full2_Test_Recovery.bak" using "SQL Server Management Studio" " Right-click on the database " 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 ", enter the path "\\ vniz - tst - bkp 01. test . local\Backup_SQL\Full 2_Test_Recovery. bak", click "Ok". In the “Destination” section, select Database "Test Recovery"
Click “Ok”
The base will be restored successfully.
Let's look at restoring a database using Transact-SQL.
Right-click on the “Test_Recovery” database and select “New Query” from the menu that appears:
In the window that appears, enter:
USE master
RESTORE DATABASE Test_Recovery
FROM DISK = "\\vniz-tst-bkp01.test.local\Backup_SQL\Full2_Test_Recovery.bak"
WITH REPLACE
The base will be restored successfully.
In this example we used the "REPLACE" parameter:
Recovery typically prevents a database from being accidentally overwritten by another database. If the database specified in the RESTORE statement already exists on the current server, and the family GUID for the specified database is different from the family GUID for the database recorded in the backup set, then the database will not be restored.
The REPLACE option overrides several important checks typically performed by a restore operation. The following checks are cancelled.
- Checking to restore over an existing database a backup created for another database.When you use the REPLACE option, a restore can write data over an existing database, regardless of which databases are contained in the backup set, even if the specified data name is different from what was written in the backup set. This may result in the database being accidentally overwritten by another database.
- Testing to recover a database that uses the full recovery model or the bulk-logged recovery model for which a tail-log backup was not taken and the STOPAT option was not applied.When you use the REPLACE option, you may lose committed data because the most recently logged data has not yet been copied to the backup.
- Overwrite existing files.
We continue to talk about backup and today we will learn create an archive of a Microsoft SQL Server 2008 database. We will consider everything as usual using examples using both a graphical interface and using an SQL query, and we will also set up automatic creating a backup using a batch file.
We will not return to the issue of the importance of database backup, since we have already raised this topic more than once, for example in the materials:
And in the last article I said that we would consider the possibility of creating an archive on the MS SQL Server 2008 DBMS, so now we will do just that.
And since there has already been a lot of theory, let’s immediately move on to practice, namely to creating a backup database.
Note! As you can see from the title of the article, we will create an archive on a Microsoft SQL 2008 DBMS using Management Studio. The server is located locally. Windows 7 OS.
How to create an archive of a SQL server database
Let's decide that we will make an archive of a test database called “test”. From the beginning, through the graphical interface, and in the process we will write a script, so that in the future you can simply launch it and not be distracted by entering all kinds of parameters.
Open Management Studio, expand « Database» , select the desired database, right-click on it, select Tasks->Create Backup
A window will open “ Database backup", where you can set archiving parameters. I just set the name " Backup Data Set", and also changed the name of the archive and the path, since by default it will be created in the Program Files folder, for example, my default path was
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\
For example, I changed it to C:\temp\ and named the archive test_arh.bak
Also if you go to the tab « Options», then you can set the setting to overwrite all data sets, now I’ll explain what this is. If you leave everything as is, i.e. add to an existing data set, then you will have one backup file, but with several copies of data sets, i.e. When restoring, you simply select the set you need. And if you put “ Overwrite all existing backup sets", then there will always be one set, then in this case you will need to create archives (say daily) with different names. I set it to overwrite, because let’s say, in the future, I plan to create archives for each day with the date indicated in the name of these archives, so that, if necessary, I can quickly copy the backup I need for a certain date to any place.
And by the way, at this point, after entering all the parameters, you can create a script in order to record it and use it in the future. To do this, simply click “ Scenario».
And as a result of this action, a request window will open, in which there will be the code for this script. We’ll come back to it a little later, but for now click “OK” and after the operation is completed you will see a window in which the result of the backup will be indicated, if everything is fine, then the following message will appear
Creating an archive of a SQL server database through a query
If you have done everything as above ( those. clicked "Script"), then you have a query window opened, in which there is actually a request to create an archive, but we will redo it a little, since I said that we plan to run it every day, so in order for the name to be appropriate, we will write the following SQL instruction .
DECLARE @path AS VARCHAR(200) SET @path = N"C:\temp\test_arh_" + CONVERT(varchar(10), getdate(), 104) + ".bak" BACKUP DATABASE TO DISK = @path WITH NOFORMAT, INIT, NAME = N"Database test", SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
And now if we run it, we will create a backup copy of the database called test_arh_ The current date.bak
Automatic backup creation on SQL server
For these purposes, MS SQL 2008 has a special feature called “ Service Plans", where you can set up a schedule for creating a backup copy of the databases, but for these purposes I suggest using a bat file to configure it in the scheduler and so that it runs every day and makes a backup of the database.
To do this, copy the SQL instruction that we discussed above and paste it into notepad ( I recommend Notepad++), then save with the extension .sql those. this script will be executed on MS Sql 2008. Then we just have to write a batch file so that it connects to the SQL server and executes our script. We also write in notepad:
SET cur_date=%date:~6.4%%date:~3.2%%date:~0.2% osql -S localhost -i C:\temp\test.sql -o C:\temp\%cur_date %_log_sql.log –E
where, I created a variable cur_date to store the current date in it, then I connect to the local server through the utility osql which uses ODBC and runs our script ( I called it test.sql), and also write down the log, where and exactly we needed our variable, that’s it, save it with the extension .bat, we create a task in the scheduler and, one might say, we forget about the process of archiving the database, well, we just periodically check whether the archive has been created or not.
This is quite enough for the basics, now you know how you can create backup copies of databases on a 2008 SQL server, in the next article we will look at how you can restore a database on MS SQL Server 2008. That's all for now! Good luck!
It is recommended to configure regular database backups(in case of hardware or software failures), and it is best to save backup copies for the last few days, for example seven (for the last week).
To do this, you can use either the task scheduler built into SQL Server - “SQL Server Agent” (not included in the free version), or the standard “Windows Scheduler” in combination with the SQLCMD.EXE utility, which allows you to run queries to SQL Server from the command line. In the scheduler, you must create at least seven jobs (one for each day of the week), each of which will (once a week) replace one of the seven files containing the corresponding database backup.
In addition, it is recommended to store backup files not only on the hard drive of the computer where SQL Server is installed, but also to duplicate them to tape or the hard drive of another computer on the network. To do this, you can either use special software that allows you to make backup copies of the entire disk, or use the same scheduler to copy files to tape or another computer (second step).
Using Windows Scheduler (free version)
To create a task in Windows Scheduler you need to:
Launch the Notepad program (Start->All Programs->Accessories->Notepad) and enter the following two lines, then save them as a batch file (*.BAT):
SQLCMD -S (local) -E -Q "BACKUP DATABASE AltaSVHDb TO DISK = "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 case of installing a named instance of SQL Server, you must specify the full name: “COMPAN_NAME\SQLEXPRESS”), "AltaSVHDb"- database name, "D:\BACKUP\ AltaSVHDb_monday.bak"- the name of the file to create a backup copy of (will vary by day of the week), "BACKUP_SERVER"- the name of the computer to which additional copying will be performed, "Folder"- a folder on this computer (it must be shared).
Launch the Task Scheduling Wizard (Control Panel->Scheduled Tasks->Add Task) and click the “Next” button:
Click the “Browse” button and specify the path to the command file (*.BAT) created in step a):
Specify a name for the task, select the “weekly” run option and click the “Next” button:
Check the box next to the desired day of the week, and in the “Start time” field specify the time when the backup process should start (usually this is done at night), then click the “Next” button:
Enter the username and password (twice) of the OS account under which the task will be executed, and click the “Next” button:
Attention! In order for the task to complete successfully, you must grant the account specified here (domain or local computer) write rights to the above folder "\\BACKUP_SERVER\Folder", as well as configure access to SQL Server itself.
Click the “Finish” button
Note. To check the functionality of the created task, you need to right-click on the task of interest in the list of tasks (Control Panel->Scheduled Tasks) and select “Run” in the context menu, then make sure that the database backup file was successfully created in the paths that were specified in step a).
Using "SQL Server Agent" (not included in the free version)
To create a job in SQL Server Agent you need to:
Launch the SQL Server Management Studio utility and connect to the server under an administrator account.
In the left part of the window, right-click on the “Server objects/Backup devices” section and select “Create a backup device” in the context menu:
In the “Device Name” field, enter the name that will be associated with the database backup file, change the path in the “File” field if necessary and click “OK”:
In the left part of the window, right-click on the “SQL Server Agent/Tasks” 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, make sure that “Transact-SQL (T-SQL) Script” is selected in the “Type” field, and enter the line in the “Command” field:
BACKUP DATABASE AltaSVHDb TO AltaSVHDb_monday WITH INIT, NOFORMAT, SKIP, NOUNLOAD
Where "AltaSVHDb"- database name, "AltaSVHDb_monday"- name of the backup device created in step c) (will vary by day of the week):
In the previous window, click the “OK” button; as a result, the following line should appear on the “Steps” page:
In order for the database backup file to be immediately copied to another computer on the network, you must repeat steps f) - h), in the “Create a job step” window, selecting the “Operating system (CmdExec)” value in the “Type” field, and specifying in the “Command” field line:
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 day of the week), "BACKUP_SERVER"- the name of the computer to which the copy will be made, "Folder"- folder on this computer (must be shared):
Note. In order for the file to be copied successfully, you must run the “SQL Server Agent” under a Windows domain account that has been granted write rights to the above-mentioned folder (see also “SQL2005_installation.doc” or “SQL2008_installation.doc”), and also configured access to the SQL itself Server (see the section “Configuring access rights to the database”, you must include this account in the “sysadmin” role on the “Server Roles” page, and do nothing on the “User Mapping” and “Protected Objects” pages).
On the “Schedules” page, click the “Create” button:
Enter a name in the “Name” field, make sure that “Recurring task” is selected in the “Schedule type” field, and “Weekly” is selected in the “Run” field. Check the box next to the desired day of the week (uncheck the rest), and in the “One-time task” field indicate the time when the backup process should start (usually this is done at night):
In the previous window, click the “OK” button; as a result, the following line should appear on the “Schedules” page:
Click the “OK” button.
Note. To check the functionality of the created task, you need to right-click on the task of interest in the “SQL Server Agent/Tasks” section and select “Run task at a step” in the context menu, select the first step of this task in the window that appears and click “OK”. Next, a window will appear showing the progress of the task. If the task ends with an error, a detailed description of the error can be seen by calling the “View Log” item in the same context menu.