We continue to talk about backups and today we will learn how to create an archive of MS SQL Server 2008 database. We will consider everything, as usual, using both the graphical interface and the SQL query as well as configure automatic backup created using the bottle.

And since the theory was already a lot of practice, let’s go straight to practice, namely to create a backup base.

Note! As you can see from the title of the article, we will do the archive on the Microsoft SQL 2008 DBMS using Management Studio. The server is located locally. Windows 7.

How to create a SQL Server database archive

Let’s define that the archive we will be doing a test base called “test”. From the beginning through the graphical interface, and in the process of doing so, we will write down the script so that we can just run it later and not be distracted by the input of any kind of parameters.

Open Management Studio, expand the “Databases”, select the desired database, right-click on it, select Tasks->Backup

You will see the “Database backup” window where you can set parameters for archiving. I have only specified the name of the “Backup Dataset” and also changed the name of the archive and the path, as by default it will be created in the folder Program Files, for example, I had the default path

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 “Parameters” item, you can set up to overwrite all data sets, now I will explain what it is. If you leave everything as it is, i.e. add to the existing data set, you will have one backup file, but with several copies of the data sets, i.e. during the recovery you will just select the required set. And if you set “Overwrite all existing backup data sets”, the set will always be the same, then in this case you will need to create archives (say, daily) with different names. I have set to overwrite it, because, for example, in the future, I plan to create archives for each day with the date in the name of these archives, in order to 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 to record it and use it further. To do this, simply click on “Script” at the top

And as a result of this action, you will see a window of requests, in which you will get the code of this script. Let’s go back to it a little bit later, but in the meantime click “OK” and after the operation is finished you will see a window where you will see the result of the backup, if everything is fine, you will see the following message

Create an archive of the SQL server database using the query

If you have done everything as above (i.e. pressed “Script”) then you have a window of queries, in which actually there is a request to create an archive, but we will modify it a little bit, because I said that we plan to run it every day, so for the name to be appropriate, we will write this SQL instruction.

declare @path as varchar(200)

set @path = N’C:\temp\test_arh_’ + CONVERT(varchar(10), getdate(), 104) + ‘.bak’

BACKUP DATABASE [test] TO DISK = @path

WITH NOFORMAT, INIT,  NAME = N’База данных test’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

And now if we run it, we will have a backup of the database with the name test_arh_Current date.bak

Automatic backup creation on SQL server

For these purposes, MS SQL 2008 has a special feature called “Maintenance Plans” where you can set up a schedule for creating a database backup, but I suggest you use a bat-file for this purpose to configure it in the schedule and that it is run every day and make a backup of the database. To do this, copy the SQL instruction we discussed above and insert it into notepad (I recommend Notepad++), then save it with the .sql extension, i.e. this script will be run on MS Sql 2008. Then we will have to write a batnik to connect to the SQL server and execute 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 the cur_date variable to store the current date in it, then connect to the local server, through the utility osql which uses ODBC and run our script (I called it test.sql), as well as write the log, where and exactly where we needed our variable, everything, save with the extension .bat, create a task in the schedule and forget about the process of archiving the database, but only periodically check whether the archive was created or not.

For the basics of this is quite enough, now you know how to create database backups on a 2008 SQL server, in the next article we will consider how to restore the database on MS Sql 2008. In the meantime, that’s it! Good luck!