Create an automatic backup SQL-database on SQL Express Edition server

Everyone is familiar with the postulate that for safe operation must be periodically create backups of their databases, but still very few people thought this topic before in a beautiful moment I lost all their achievements.

in this article, our blog I will try to give users the software Alta GTD the PRO, a series of small, but very important recommendations that how to set up an automatic backup of the database using only standard tools MS SQL Server Express Edition 2005/2008 and MS Windows operating system.

Everyone is familiar with the postulate that for safe operation must be backed up periodically their databases, but still very few people thought on this subject before in a beautiful moment lost all their achievements.

in this article, our blog I will try to give users the software etc. odukta Alta GTE PRO» a number of small, but very important recommendations on how to set up an automatic backup of the database using only standard tools MS SQL Server Express Edition 2005/2008 and MS Windows operating system.

Step 1, Create a text file (for example, backupGTD.txt) as follows,

sqlcmd -S DECLSERVER SQLGTD -E -Q «declare @s varchar (255) set @ s = ‘ E, backup GTD_ ‘+ convert (varchar (1), datepart (dw, getdate ())) +’ .bak ‘backup database GTD to disk = @s with init, noformat, skip, nounload »

sqlcmd allows you to enter instructions Transact-SQL, the system procedures, and script files from the command line editor in SQLCMD mode queries,

  • -S – specifies the name of the server, server [ instance_name] ,
  • DECLSERVER SQLGTD – the server name / instance name of the spinning base,
  • -E – used for connecting eniya with SQL server instead of a user name and password, trusted connection,
  • -Q «cmdlinequery« – you run the program sqlcmd It makes a request, but a way out of the program at the end of its execution is not performed. multiple queries separated by semicolons can be performed. Enclose a request in quotes, as shown above,
  • declare – declare a variable s, variable name always starts with @, so @s < / em>. In our case, the @s – the folder (disk) storage backups,
  • varchar (n) – specifies the type of variable @s as a string with the longest line n, characters in example 255,
  • set – sets the value of the variable @s , in the example backup folder on drive E ( E, backup ), is set forth the name of the backup file, where a set of functions convert (varchar (1), datepart (dw, getdate ())) returns in text format with a length of 1 symbol of the current day of the week (Monday – one , Tuesday – 2 , and so on. d.) and the extension added to bak . At the end we will get a file called GTD_NomerDnyaNedeli.bak ,
  • backup – creates a backup,
  • database – indicates the creation of a backup the entire database,
  • GTD – in our example, the name of database on SQL-server,
  • to disk – indicates the type of backup storage devices, hard disk file, and contains the variable @s < / em>, which is dedicated to the path and name of the file to be created,
  • with init, noformat, skip, nounload – indicates that it is necessary to dub data in a circle with a redefinition of titles that will allow us to have a 7 backup files for each day of the week, rewritable in a circle.

You can use the other functions, if necessary, such as compression, see . certificate on demand and functions of Transact-SQL.

Step 2, Change the extension of the text file in the .cmd

As a result, we get the file backupGTD.cmd . Run the batch file must be created with the machine where you installed the database MS SQL

Step 3, Automate the process

Consider this step the example of MS Windows Server 2008,. Server Manager -> Configuration -> scheduler -> task scheduler Library

.