I am using Microsoft SQL Server 2008 Express but you should be able to use these instructions for all versions of Microsoft SQL Server 2005 and Microsoft SQL Server 2008.
In my case, the database files are located in the following directory:
As you may already know, you cannot backup the *.MDF and *.LDF database files directly because they are attached to the SQL Server. You could detach them, back them up, and then re-attach them but then your database will be offline for a period of time.
To solve this problem, we first need to create a SQL file that can be used later to automate our database backup. Here’s how we can do that:
CREATE AN SQL FILE
- Login to Microsoft SQL Server Management Studio.
- Right click on your database from the “Databases” node.
- Select “Tasks” -> “Back up…”
- You are now presented with the “Back up Database” window. In this window you can specify your backup settings. At a minimum, you need to create a destination for your backup. Click the “Add” button and specify a full path name for your database backup in the “File name” field. You will need to use this path name later, so let’s take note of it.
- You can choose any other backup options that are important to you. Over on the left hand side is “Options”. I would click on that and decide whether you want to use “append to an existing backup set” or “overwrite all existing backup sets”.
- Once you are finished with your settings, you need to create an SQL file which will be used later for the automated database backup. To create that SQL file, click on the arrow next to “Script” at the top of the window. There will be an option for “Script action to File”. Go ahead and select that option. You will then be able to save your SQL Server Script File. Take note of where you have saved this file as well.
CREATE A BATCH FILE
Now that we have created the SQL file, we need to create a batch file that can be automatically executed by the Task Scheduler. To create this file, do the following:
- Open Notepad and enter the following:
CODEsqlcmd -S .\SQLEXPRESS -i “C:\Users\Administrator\Documents\Backup.sql”
Of course, substitute the pathname with the pathname of where you saved your SQL file.
- Save this file and take note of where you saved it.
- Now, using Windows Explorer, go to the directory of where you created this file and rename it to have a .bat filename extension instead of .txt.
SET BATCH FILE TO RUN IN WINDOWS TASK SCHEDULER
We are now ready to add the batch file to the Task Scheduler. Rather than using the Task Scheduler GUI, I prefer to do it on the command line. You can open a command prompt as Administrator or if you are already running as Administrator, you can use Start -> Run. Either way, enter the following:
Again, substitute the pathname above with that of your batch file.
Also, set the time specified to something that is desirable for you. In the above example, we run this task at 3:30 AM.
You may be wondering why I set the Task Scheduler to run a batch file instead of the sqlcmd directly. The reason is two-fold:
1. I could not get the Task Scheduler to run the sqlcmd for some reason.
2. In my batch file I also perform other tasks to make my life easier.
Well I hope this helps someone… Took me a while to figure it out on my own.