{"id":1252,"date":"2014-01-22T09:06:38","date_gmt":"2014-01-22T14:06:38","guid":{"rendered":"http:\/\/swildow.darktech.org\/wp\/?p=1252"},"modified":"2014-01-22T09:06:38","modified_gmt":"2014-01-22T14:06:38","slug":"schedule-a-daily-backup-with-sql-server-express","status":"publish","type":"post","link":"http:\/\/www.wildow.com\/blog\/?p=1252","title":{"rendered":"Schedule a daily backup with SQL Server Express"},"content":{"rendered":"
\n

Schedule a daily backup with SQL Server Express<\/a><\/h1>\n<\/div>\n
By\u00a0outcoldman<\/a>,\u00a029 Sep 2010<\/div>\n\n\n\n
<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

As you know, Microsoft SQL Server Express Edition does not have\u00a0Agent Service<\/a>, which can be used to schedule daily backups or other periodic administrative tasks. But you can use for this standard Windows Schedule tool.<\/p>\n

SQL Server Express Edition has some limitations, but anyway it is a very powerful tool for storing data. For most small or medium size projects, you can use it without any restrictions. But Express edition has a little problem, it does not have a SQL Server Agent. However, you can use SQLCMD command line tool and standard Scheduled Tasks for Windows instead of it. If you want to set automating administrative periodic task, first of all, you need to write a SQL script. We want to solve a problem: write a script which will do a daily backup. Of course, you can use Microsoft Management Studio for generating current script (as you know, you can download Management Studio Express Edition too from the Microsoft site): just click \u201cScript Actions to\u2026\u201d instead of OK button at \u201cBack Up Database\u201d dialog box and you will get a script.<\/p>\n

\"\"For daily backups, I usually use this script:<\/p>\n

\"\"\u00a0Collapse\u00a0|\u00a0Copy Code<\/a><\/div>\n
DECLARE<\/span \/> @pathName NVARCHAR<\/span \/>(512<\/span \/>) \r\nSET<\/span \/> @pathName = '<\/span \/>D:\\Backup\\db_backup_'<\/span \/> + Convert<\/span \/>(varchar<\/span \/>(8<\/span \/>), GETDATE(), 112<\/span \/>) + '<\/span \/>.bak'<\/span \/> \r\nBACKUP<\/span \/> DATABASE<\/span \/> [MyDataBase] TO<\/span \/>  DISK<\/span \/> = @pathName WITH<\/span \/> NOFORMAT, NOINIT,  _\r\n\tNAME = N'<\/span \/>db_backup'<\/span \/>, SKIP, NOREWIND, NOUNLOAD,  STATS = 10<\/span \/><\/pre>\n
This script makes a backup file with name\u00a0db_backup_YYYYDDMM.bak<\/em>\u00a0where YYYYDDMM is the current date. This format will give you an opportunity to have every day backup at separate files. Correct this script and try to run it, check that you will get a backup file (I set \u201cd:\\backup<\/em>\u201d folder for files, change it if you want). Next step \u2013 save this script to file\u00a0schedule.sql<\/em>\u00a0and put it to \u201cc:\\scheduled tasks\\<\/em>\u201d (you can choose any other names for script file and folders, just make sure that you will change it at all places where it will be used). At this folder create a file\u00a0backup.bat<\/em>\u00a0with next contain:<\/p>\n
\"\"\u00a0Collapse\u00a0|\u00a0Copy Code<\/a><\/div>\n
sqlcmd -S SEVERNAME -U UserName -P Password -i schedule.sql\r\n7z a -tzip D:\\Backup \\db_backup_%date%.zip -i! D:\\Backup\\db_backup_*.bak\r\ndel d:\\Backup\\db_backup_*.bak<\/pre>\n
Where:\u00a0SERVERNAME<\/em>\u00a0\u2013 database instance name (server name),\u00a0UserName<\/em>\u00a0\u2013 SQL user, which has privileges for making backups,\u00a0Password<\/em>\u00a0\u2013 password of this user,\u00a0schedule.sql<\/em>\u00a0\u2013 name of the script with we created in the previous step. Second and third lines of this bat script do an archive of this backup file and then delete the backup file. I use 7z (http:\/\/www.7-zip.org\/<\/a>) utility for this. If you want, you can use any other archive tool, or maybe leave backup file unzipped (Microsoft SQL Server 2008 and higher has an opportunity to backup database with compression, so you can just set it in SQL script). If you will use 7z like me, you need to set full path to\u00a07z.exe<\/em>\u00a0tool or you can just copy7z.exe<\/em>\u00a0and\u00a07z.dll\u00a0<\/em>files from installed folder of 7z tool to folder where this bat script will be located. And make sure that if you used other folder names or file names in the previous steps, you change these names in this bat script too. You can try to run this bat script and check that the backup file will be created and zipped.<\/p>\n
The last step is to create a schedule task. In each Windows version, it can be a different way to create this task.\u00a0This is how<\/a>\u00a0to create schedule task for Windows XP,\u00a0this link<\/a>\u00a0is how to for Windows 7. For other versions, I think you can find a how-to at Windows help or other blog posts.<\/p>\n
When you will create this Windows task, you should check that the user by whom these tasks will be launched has rights to create files at folders, where script will put backups, and this user has the right to execute bat file.<\/p>\n
Like this, you can run not only daily backup. You can execute some procedures, re-index tables or maybe some other administrative stuff \u2013 just place what you want at\u00a0schedule.sql<\/em>\u00a0script.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Schedule a daily backup with SQL Server Express By\u00a0outcoldman,\u00a029 Sep 2010 As you know, Microsoft SQL Server Express Edition does not have\u00a0Agent Service, which can be used to schedule daily backups or other periodic administrative tasks. But you can use … Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1252","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1252","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1252"}],"version-history":[{"count":1,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1252\/revisions"}],"predecessor-version":[{"id":1253,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1252\/revisions\/1253"}],"wp:attachment":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1252"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}