How to create an automatic SQL 2005 Express Backup

How to create an automatic SQL 2005 Express backup from your database.

To do this you have to do the following steps.

Step 1: Create a .sql file in a folder which is easy to remember. [ for exambe Databasestore.sql ]

Step 2: You need enter the following script into the Databasestore.sql file ( you can edit this file with notepad )

SET @BackupSQL = N'BACKUP DATABASE [yourdatabase] TO DISK=N''D:\Backuplocation\backupfile_' + CONVERT(VARCHAR(50), GETDATE(), 112) + '.bak'' WITH CHECKSUM, STATS=5';
EXECUTE sp_executesql @BackupSQL

Save this file

Step 3: Test if the file works.

Go To Start > Run > CMD
C:\SQLCMD -s <instance> -i c:\script-location\DatabaseStore.sql

if the script run’s good you will see some information like the following input

C:\>sqlcmd -s <instancename> -i C:\Scripts\DatastoreBackup.sql
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
Processed 1480 pages for database 'databasename', file 'databasename_Data' on file
100 percent processed.
Processed 1 pages for database 'databasename', file 'databasename_Log' on file 1.
BACKUP DATABASE successfully processed 1481 pages in 0.154 seconds (78.781 MB/se


You can add this schedule when it works to the TaskSchedular on your computer or server.
Use the following command

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE -s <instance> -i "c:\Scripts\DatastoreBackup.sql"

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s