I have just changed the db for a small app from using an MS ACCESS db (terrible thing!) to SSE 2008 R2 DB. Previously, they used to just copy the ACCESS db file each evening to a flash drive, and keep that off site. They have been using the app for a year now, and the SSE backup file is only about 20MB - in other words not that much data to backup. The server instance only has one database, and one additional user, no views or stored procedures or anything like that
1) What exactly do I need to back up? The database, system databases (master, model, msdb etc) log files?
2) How should I do the backup? (Been thinking along the lines of a backup command that can be run from the app (SMO), or possibly having a table in the DB with last backup date/time, and if a user logs on, and the last backup is older that a day, it makes a new backup. Or is there a better way? Can you schedule a job on SSE to do this daily?
3) I'm also not sure what options to use with respect to:
a) Backup Type (Full or Incremental?)
b) Copy-only Backup (No?)
c) Backup to existing medias set (yes) - Overwrite or Append?
4) The code I've tested using SMO
Backup bkp = new Backup();
bkp.Action = BackupActionType.Database;
bkp.Database = databaseName;
bkp.Incremental = chkIncremental.Checked;
Basically I want to end up with a backup (that can possibly be copied to some or other storage device as well for off site storage - flashdisk or portable drive) that in that case of the sever crashing completely, that I could restore to at worst a new instance of SSE?
Am i on the right track here?
Any other suggestions welcome!
modified 1-May-13 2:23am.