In this article I will present a simple way to backup active databases from SQL Express without the need for the SQL Agent or shutting down the SQL Server database engine. For those that are not aware, the SQL Agent is not available with the Express editions of SQL Server. Never-the-less, SQL Express is quite powerful and is more frequently deployed in the enterprise than not, hence the need for third party software backup software/agents or free methods…such as this one.
This method uses OSQL and an input .sql file containing the SQL commands needed to backup the database(s). If you are unfamilar with the necessary SQL commands to back up a database, we can generate the script from the SQL Server Management Studio Express application.
1. Use SQL Management Server to script a Backup Job similar to the one below. The method for creating a script instead of performing the actual action is to begin the Backup task and use the “Script” button, choose Script Action to File:
BACKUP DATABASE [(database name)] TO DISK = N'C:\SQL Backups\(file name).bak' WITH NOFORMAT, NOINIT, NAME = N'(backup name)-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
Save the file to the location where you can find and use the file in the future. In my example, I saved all scripts to the C:\SQL Backups\ folder. If you use my example, make sure you replace the fields marked with () and omit the parentheses.
2. Verify that the SQL Server allows Windows and SQL Authentication. In the SQL Server Management Studio Express application, right click on the server, choose properties, and then click on the security tab. This change will require a restart of the SQL Server engine.
3. Create a local SQL user with the necessary access to the databases that you will be backing up. In my example I assigned sysadmin to the backup user, which I named SQLBackups.
4. Now we need to create a scheduled job in Windows “Scheduled Tasks” to run at the specified time of our choosing. Although you may place the full command in the scheduled task program text box, I would recommend placing the following in a batch file (stored with the .sql file):
@ECHO OFF del "[FULL PATH TO SQL BACKUP FILE]" osql -S [SERVER]\[INSTANCE] -U [USERNAME] -P [PASSWORD] -i "[FULL PATH TO SQL FILE]"
When replacing the values to match your configuration, omit the brackets []. In addition, ensure your have the SERVER\INSTANCE correct.
5. Thats it!
Common Error:
Login failed for user 'SQLBackups'. The user is not associated with a trusted SQL Server connection.
You must enable Windows Auth and SQL Auth in server properties and then restart the SQL server service.
IT is not just a job but also a passion. Everything I have accomplished, both personally and professionally, has been generally entertaining, bordering on fun. Some of my projects, such as working with SharePoint Services workflow actions in Visual Studio or building a custom iSCSI SAN using the OpenSolaris, ZFS and COMSTAR, has been quite rewarding. You may think nerd...I think developing a new trend!
@ECHO OFF
del “[FULL PATH TO SQL FILE]”
osql -S [SERVER]\[INSTANCE] -U [USERNAME] -P [PASSWORD] -i “[FULL PATH TO SQL FILE]“
If you del “[FULL PATH TO SQL FILE]“ surely it won’t be there when you address it with the next line. Shouldn’t it be something more like delete the old backup file then point osql at the script to create the new one?
James,
Thank you for the post, I’ve updated the example to reference [FULL PATH TO SQL BACKUP FILE] instead. The original intent was to delete the .bak file prior to running the script again.
-Aaron