SSE 2008 R2 Backup Strategy
-
Hi 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.Devices.AddDevice(fileName, DeviceType.File); bkp.Incremental = chkIncremental.Checked; bkp.SqlBackup(srv); 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!
-
Hi 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.Devices.AddDevice(fileName, DeviceType.File); bkp.Incremental = chkIncremental.Checked; bkp.SqlBackup(srv); 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!
Without going into details of which backup strategy you should chose - there are lots of articles out there that can do a much better job than I can - do make sure that you test your backups. I do a weekly restore to check that the backups are working correctly - I even went as far as to deliberately corrupt the database(not an easy thing to do I can assure you) to check that a restore worked ok. Remember - you backups are only as good as the state they return your system to should you experience a catastrophic hardware failure. I would also recommend taking an image of your server every month - as in the case of a failure it might be easier to restore an image then backups on top of that rather that rebuild a Windows server from scratch...
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Without going into details of which backup strategy you should chose - there are lots of articles out there that can do a much better job than I can - do make sure that you test your backups. I do a weekly restore to check that the backups are working correctly - I even went as far as to deliberately corrupt the database(not an easy thing to do I can assure you) to check that a restore worked ok. Remember - you backups are only as good as the state they return your system to should you experience a catastrophic hardware failure. I would also recommend taking an image of your server every month - as in the case of a failure it might be easier to restore an image then backups on top of that rather that rebuild a Windows server from scratch...
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Hi Guy Thanks for your reply. I'm not working for the company, I only developed an app for their stock control. They don't have IT staff on site, and I'm really not sure what sort of backups they do on the servers. (They have two boxes both running Small Business Server - and I installed and instance of SSE for my app on one of them) All I want is to be able to restore the data for my application. Even if that means I install a new instance of SSE on another machine, and restore the data that was backed up. When you say you restore a backup weekly to test, where do restore the backup to? Have you got a duplicate database that you use to test the restore? What sort of things do you check to see that restore was successful - If it restores without Errors, check row counts on the tables?
-
Hi Guy Thanks for your reply. I'm not working for the company, I only developed an app for their stock control. They don't have IT staff on site, and I'm really not sure what sort of backups they do on the servers. (They have two boxes both running Small Business Server - and I installed and instance of SSE for my app on one of them) All I want is to be able to restore the data for my application. Even if that means I install a new instance of SSE on another machine, and restore the data that was backed up. When you say you restore a backup weekly to test, where do restore the backup to? Have you got a duplicate database that you use to test the restore? What sort of things do you check to see that restore was successful - If it restores without Errors, check row counts on the tables?
I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps on the restored data as a sort of sniff test. SQL Server is 'generally' good at telling you if a backup restored with corruption - you could run a select query on the major tables as a sniff test, just to ensure that what comes back looks ok. As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA(which is another point - make sure that you have an agreed SLA on how long a restore of a broken system will take and double the time you think it will take you, I can't stress how important this is because when things go wrong people behave irrationally and you need to be able to hold up that piece of paper to give yourself some space, in order to get on with fixing the systems). A few useful sites worth reading. Backups[^] Validating a backup[^](if you are not subscribed it is well worth joining this site) Database integrity[^] I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive. Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps on the restored data as a sort of sniff test. SQL Server is 'generally' good at telling you if a backup restored with corruption - you could run a select query on the major tables as a sniff test, just to ensure that what comes back looks ok. As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA(which is another point - make sure that you have an agreed SLA on how long a restore of a broken system will take and double the time you think it will take you, I can't stress how important this is because when things go wrong people behave irrationally and you need to be able to hold up that piece of paper to give yourself some space, in order to get on with fixing the systems). A few useful sites worth reading. Backups[^] Validating a backup[^](if you are not subscribed it is well worth joining this site) Database integrity[^] I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive. Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps
This should be easy enough to implement... I think.... (Well at least a duplicate database on the same server instance)
GuyThiebaut wrote:
As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA
Good point - the guy is a mate, but he should know what is a reasonable recovery time...
GuyThiebaut wrote:
I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive.
I will suggest that he implements this (I think he brings in a consultant to help out with servers etc)
GuyThiebaut wrote:
Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).
Not entirely sure what you mean here? The app is a standalone windows forms app, installed on the workstations, so I guess that should be fine. Thanks for the links - will have a look shortly
-
GuyThiebaut wrote:
I restore the backup to a test system(so yes a duplicate database) then I test a series of .Net apps
This should be easy enough to implement... I think.... (Well at least a duplicate database on the same server instance)
GuyThiebaut wrote:
As a DBA you want to make sure that when the SHTF you can get the systems back to working within an agreed SLA
Good point - the guy is a mate, but he should know what is a reasonable recovery time...
GuyThiebaut wrote:
I would strongly recommend an image backup every month with daily incremental image backups - disk space is cheap and rebuilding systems is expensive.
I will suggest that he implements this (I think he brings in a consultant to help out with servers etc)
GuyThiebaut wrote:
Another tip - if you are not running this all on a virtual machine, ensure that the SQL Server install is on a different disk to the software accessing it(this will further ensure its integrity and speed up access).
Not entirely sure what you mean here? The app is a standalone windows forms app, installed on the workstations, so I guess that should be fine. Thanks for the links - will have a look shortly
Richard.Berry100 wrote:
the guy is a mate,
Even more important to have an agreed SLA as it is good to be clear on where the boundaries of being a mate and being an employer lie ;)
Richard.Berry100 wrote:
Not entirely sure what you mean here?
I take it there there is one central instance of an SQL Server rather than an instance on each workstation. If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Richard.Berry100 wrote:
the guy is a mate,
Even more important to have an agreed SLA as it is good to be clear on where the boundaries of being a mate and being an employer lie ;)
Richard.Berry100 wrote:
Not entirely sure what you mean here?
I take it there there is one central instance of an SQL Server rather than an instance on each workstation. If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
I take it there there is one central instance of an SQL Server rather than an instance on each workstation.
If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense?Yes - one server instance on the server. Any yes I see what you mean. It makes sense, and I do remember reading something like that, but when I installed, I seemed to have missed where I could specify a different install location, so it installed on the C: drive (default).. At the moment there are way more serious performance hits in the app itself (populating too many datagrid rows at a time etc) - Only realized this when I got a copy of their data a year after the initial installation... I'd been testing with a bare bones DB on my machine - so yeah what those poor users have been going though - aw shame!