SQL Server Express is great for small business with small data requirements. It is essentially a scaled down version of SQL Server that is free. However, because it is throttled down there are some features that are missing...one such feature is the SQL Server Agent. This service is responsible for automating SQL Server tasks, such as automatically backing up your databases. While there are plenty of tools out there that you can use to automate the backup of your SQL Server database....why pay for them? Today I am going to show you how to do it with the tools that are available with your windows operating system.
The first step in this process is the TSQL code to actually do the database backup. In my example I want to take a full backup everyday with a different file name appended to the end of the file path. In this example, my database is named Express and I am backing the files up to a folder path on the E drive of a server named db1.
Here's the code from the above screenshot. You can copy and paste this code into a new query editor window in SQL Server Management Studio.
--FullBackup.sql
DECLARE @Path NVARCHAR(1000), @FileName NVARCHAR(255), @FullPath NVARCHAR(1255)
SET @Path = '\\db1\E$\SQLServer\2008\Backups\'
SET @FileName = 'Express_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + '.bak'
SET @FullPath = @Path + @FileName
BACKUP DATABASE Express
TO DISK = @FullPath
WITH INIT
I'll need to save this file because I will reference it later when I create a Windows Scheduled Task. To save the file go to the File menu in SQL Server Management Studio and choose Save SQLQuery1.sql As...
For this example, I want to save the file to the E:\SQLExpressBackup\FullBackup.sql. This file is stored on a server named db2.
Now I need a way to automate the running of the above script that I created. If I had the SQL Server Agent service available, I could create a new Job and post the code in it. Since I do not have this luxury, I'll need an alternate method. This is where SQLCMD comes into play.
SQLCMD is a command line utility that you can use to connect to SQL Server instances and issue commands against the service. You can do pretty much everything in it as you can in SQL Server Management Studio, but without all of the bells and whistles. In the screenshot and script below, I am creating a batch file on the db2 machine which connects to my db2\SQL2008 instance using Windows Authentication and issues the backup file from above against the server.
Here's the code snippet from the batch file.
sqlcmd -S db2\SQL2008 -u -i E:\SQLExpressBackup\FullBackup.sql
Now I just need to save my batch file.
OK, I am done w/ the portion that does the work of backing up the SQL Server database. All that is left to do is to setup a Windows Scheduled task that will execute the batch file once a night to run the script which will create the backup file.
You can find the Scheduled Tasks folder under the Accessories/SystemTools folder in your Start Menu. Create a new task and give it a name.
The next step is to set when you want the task to run. I want my full backs to run Daily.
I then need to set when I want the task to run. Since there is very little load on my system around midnight every night, this seems like the optimal time to create the backup.
I'll want to start a new program when it runs, in this case a batch file.
I can then select the location of the batch file that I want to execute. Remember, this batch file contains the SQLCMD code that connects to my SQL Server instance and runs the script which will backup my database.
When I save the task, it will ask me for a user name and password for which I want the task to run under. I just need to enter my credentials and I am set.
All Done
Well, that is really all there is to it. Even though SQL Server Express doesn't come with a native tool to automate the backup of SQL Server databases, it is easily accomplished with some simple TSQL code and a Windows Scheduled Task. I hope you find this SQL Express tip handy!
Tim
Posted
23 Feb 2009 8:09 PM
by
TimChapman