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 )
DECLARE @BackupSQL NVARCHAR(max); 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:\ C:\script-location 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 1. 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 c). C:\>
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"