Backup database(s)


If you are using the database manager from Teratrax (see also Database manager) then you have the possibility to create a backup of a selected database. You can not schedule the backup because the Express version doesn't come with the nessecary SQL Scheduler.
But you can us the windows own scheduler and a old fashion batch file to create automatic backups. The next tip is provided by Teratrex (thanks for the good support!)

first create a text file and add the following sql code. Just change all MyDB with the relevant name of your database. And don't forget to change the file path (c:\) to your path.
DECLARE @filename varchar(255)
SELECT @filename = 'C:\MyDBBackup_' + CONVERT(char(4), YEAR(GETDATE()))
   + '_' + RTRIM(CONVERT(char(2), MONTH(GETDATE())))
   + '_' + RTRIM(CONVERT(char(2), DAY(GETDATE()))) 
BACKUP DATABASE [MyDB] TO DISK = @filename 
   WITH INIT, NOUNLOAD, NAME = N'MyDB backup', NOSKIP, STATS = 10, NOFORMAT 
RESTORE VERIFYONLY FROM DISK = @filename
GO
Save the file with a relevant name and the extention "sql": MyDB_backup.sql
Do this for all databases you want to include in the backup process.

Then create a batch file (extention .bat). Include in the file the next line:
OSQL -U[user] -P[pass] -i[file]
[user] is the username of the relevant db owner
[pass] is the associated password
[file] is the filename of the .sql file you have created in the first step

If you have created more backup files (for each database you want to include) then - of course - you must include as many lines as you have backup files...

The last step is setup the windows scheduler. In the Help of your OS you will find the nessecary information.
Don't forget to run the scheduled task once manually just to see if it's working alright.


Return