This article describes, how to automate database backup in SQL Express with mixed Windows and SQL Authentication running on Windows 2008 Server. This material was tested with Microsoft SQL Server 2008 R2 Express.
SQL Server 2008 Enterprise comes with Maintenance Plan feature. That feature allows amazing flexibility and auditing. SQL Express edition lacks this feature. But you still need a backup.
Backup automation with SQL Express consist of 3 steps:
(1) Write the script to backup all your databases.
(2) Test the script by initiating it from a command prompt.
(3) Schedule the script using Task Scheduler.
Here is each step in detail.
(1) Write the Script
This script overwrites any previous backup sets found on the disk with the same name.
If you give your script a *.SQL extension, its default editor will be a colorful SQL Manager.
You can schedule this script to run every day, but after a year of running your will have 4 different backup sets to go back to.
This scripts creates 4 different backup files every year. Let’s say you are in April of 2011. Your latest back will be stored in YourDB2.BAK file, but you could go back as far as last backup in 2010 third quarter stored in file YourDB3.BAK.
DECLARE @dbName VARCHAR(33) -- database name DECLARE @path VARCHAR(99) -- backup path DECLARE @fileName VARCHAR(99) -- backup file name DECLARE @fileQuarter CHAR(1) -- variable portion of file name SET @path = 'C:\_BACKUP\SQL\' -- Returns a Numeral from 1 to 4 -- Each Quarter you get a new backup file name SELECT @fileQuarter = CONVERT (char(1),(MONTH(GETDATE())+2)/3, 112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases -- Exclude all System Databases, if Needed - 'master','model','msdb' WHERE name NOT IN ('tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @dbName WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @dbName + '_' + @fileQuarter + '.BAK' -- Use INIT and SKIP to overwrite previous sets BACKUP DATABASE @dbName TO DISK = @fileName WITH INIT, SKIP FETCH NEXT FROM db_cursor INTO @dbName END CLOSE db_cursor DEALLOCATE db_cursor
|Simple script to backup all SQL Server databases||http://www.mssqltips.com/tip.asp?tip=1070|
|Using the sqlcmd Utility||http://msdn.microsoft.com/en-us/library/ms180944.aspx|
(2) Test the script
To test the script you need to exit from SQL Server Studio.
In the command bellow SQL_Instance is usually SQLEXPRESS.
SQLCMD -E -S .\SQL_Instance
-i X:\_BACKUP\SQL\_Backup.sql -o X:\_BACKUP\SQL\Log.txt
You should see multiple outputs stating “BACKUP DATABASE successfully processed …”
If you see an error message:
Sqlcmd: Error: Connection failure. SQL Native Client is not installed correctly.
it might mean that an SQL Server Agent service is disabled for your SQL instance. First, try to enabling the service and starting it. If this doesn’t work, uninstall Microsoft SQL Server2008 Native Client and run SQL setup again. Try to start the service again.
(3) Schedule the Script
Run taskschd.msc and schedule a new task. You can schedule to run this script every day. Entire third quarter the script will overwrite YourDB3.BAK file.
Surprisingly that step also took a lot of time and fine tuning to begin to work. First of all, I had to go back to previous command and add options -E and -o. Secondly, it still wouldn’t with return error 1. So, if you are getting any problems with this step, try 3 things:
– start Task Scheduled as an Administrator
– Pick option Run whether user is logged on or not and
– Check Run with highest privileges check box
Note: you have to exclude ‘tempdb’ because otherwise you will keep getting this error message:
Msg 3147, Level 16, State 3, Server Server\Instance, Line 24 Backup and restore operations are not allowed on database tempdb. Msg 3013, Level 16, State 1, Server Server\Instance, Line 24 BACKUP DATABASE is terminating abnormally.