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!
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.