I have a SQL query written and saved in SSMS that backs up my database. I try to remember to run this every time I leave the program but as memory shrinks with age, I forget enough to concern me.
I would like to incorporate this code into my vb program at the point that I am closing it. I am not sure how to go about this.
I am using VS/VB 2015,Community, SQL Express 2014
This ib the Query:
USE [D:\DATA FILES 092015\DVD_LIST.MDF]
ALTER DATABASE "D:\DATA FILES 092015\DVD_LIST.MDF" SET RECOVERY FULL;
GO
BACKUP DATABASE "D:\DATA FILES 092015\DVD_LIST.MDF" TO DISK = 'G:\DATA FILES\SQLSERVERBackups\DVD_LISTFULLRM.bak' WITH FORMAT;
GO
BACKUP LOG "D:\DATA FILES 092015\DVD_LIST.MDF" TO DISK = 'G:\DATA FILES\SQLSERVERBackups\DVD_LISTFULLRMLog.bak';
GO
why not just set a task scheduler task to run the backup (using sqlcmd) regularly?
If you are only doing manual, "on-demand", backups then using FULL RECOVERY model is not adding much, and may give you run-away disk usage issues (e.g. if you don't backup the log for a prolonged period). It would give you the ability to restore to point-in-time (e.g. if you accidentally deleted some customer records), but it will only save you from disaster if you are able to backup the log file AFTER the disaster / disk corruption / etc.
If you are toggling the Recovery Model?? just to run the backup then you don't need to do that, and the log backup won't contain anything useful (i.e. if you toggle to FULL, make the backup, then toggle back to SIMPLE)
The more common approach is to schedule the backup so that the log is backed up every, say, 10 minutes and the whole database, say, in the middle of the night. If you turn your computer off then the "overdue" backup would be done when SQL was next re-started.
A log backup every 10 minutes means that if your database was trashed, for some reason, your worse-case data loss is 10 minutes (assuming that the backup is not lost in the same disaster as the database! we copy our 10-minute backups to another computer to spread our chances, and then (less frequently) we copy the backup file to tape and offsite.