- Obtain SQL Management Studio Express from Microsoft. Free download is here.
- Login using Administrator credentials (Administrator has system admin over the SQL2005 server by default)
- Expand Databases from the Object Explorer, select the desired databases
- Right-click and select Tasks then Back Up…
- Select the appropriate database backup options from the dialogue
- Press 'Ctrl+Shift+F' to save the script options to a file, and save it to a known location, preferably on the local server.
- Edit the script file to reflect the following:
BACKUP DATABASE [DatabasetoBackup] TO DISK = N'C:\backup\sql\DatabasetoBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
...where 'DatabaseToBackup' is the name of the desired database, listed above in Step 7. You may also want to add the database name to the 'NAME' statement in the command, eg PLUS-Database-Backup. Also note that the machine user that SQL Express is running under (Usually NETWORK SERVICE by default) needs write/modify permissions to the directory your database backup file will be located. C:\backup\sql\ in the example above.
- Test this script by logging into SQL Management Studio as the backup service account, and run the script. Any problems or permissions errors will show up here and you can ensure your scheduled backups will run successfully in the future.
- Open Windows Task Scheduler (Control Panel then Scheduled Tasks) and create a new Scheduled Task.
- Browse to C:\Program Files\Microsoft SQL Server\90\Tools\Binn\ and select SQLCMD.exe
- Name the job and select the schedule (Daily, Weekly, monthly, etc)
- Edit the Start Time and desired days of the week (for a Weekly schedule) or desired start time (for Daily schedule)
- Enter a username and password to execute the scheduled task. This must be a Windows local or domain user, not a SQL user.
- Check the Advanced Permissions checkbox.
- In the Run dialog, append the existing SQLCMD command with the following:
"-S [SERVER] -U dbbackup -P [PASSWORD] -i C:\PathToScript\NameOfBackupScript.sql"
Your final command will look like this:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S [SERVER] -E -i C:\backup\sql\SQLExpressBackup.sql
- Note the location of the quote marks surrounding the command path. They should not include the command arguments.
- The '-E' switch implies Trusted Authentication, which can only be used by the local Administrator account, which is automatically a member of the sysadmins group in SQL. You can also add another Windows account to the sysadmins group and use it as the backup user.
- For a local database server, use –S localhost
- Adjust Security permissions for the job so only trusted users have access, since the SQL backup users' password is stored in plaintext.
You can verify the backup job runs successfully by right-clicking on the Job in the Scheduled Tasks folder and click Run. Note: If the job is set to run as the locally logged on user, you will see a command prompt pop-up window during the job process.