Olaf Doschke
Programmer
I "inherited" a software using an SQL Server backend, which is in simple recovery mode.
Daily backups are simply made to the same BAK file with the BACKUP command:
A little simplified, the path is configurable, but obviously has to exist on the server and isn't a moving target, once configured always the same BAK is used.
The customer is fine with this simple backup mechanism and also knows how to restore in case there are problems. So there is no real problem with that, but the owner was having the idea to rename TheDatabase.bak to TheDatabase.bak2 before doing the next backup.
I think there are ways to have two or more backups accumulating in the one bak file but I do agree in this simple mode and for the sake of having a fresh new file and a separate older one is not the worst idea, even though only putting the bak somewhere completely separate (eg cloud storage, overall file backup) would give an extra safety.
The software is used in a small office on only 2 stations, one of which "is" the server and so only there the frontend application can actually do such a file renaming at all. Unless I'd enable xp_cmdshell the client application could also command the SQL Server to do that from both clients.
I'm looking for other simple ideas for such a simple office setup. The database is small (~200MB) in comparison to what I work on for companies, with the big difference those have their DevOps and network and database admins to care for that, this customer hasn't.
Should I change the recovery mode to full, obviously? I don't see pressing reasons to do so. It would just enable a restore at any point in time, but daily backups are considered good enough.
I thought along the lines of simply giving the backups a date in the filename itself and the client directly at the server could delete the one that got two days old or keep weekly, monthly, quarterly, yearly backups in a grandfather-father-son rotation principle.
Bye, Olaf.
Olaf Doschke Software Engineering
Daily backups are simply made to the same BAK file with the BACKUP command:
Code:
BACKUP DATABASE TheDatabase TO DISK = 'C:\backup\TheDatabase.bak' WITH INIT,SKIPA little simplified, the path is configurable, but obviously has to exist on the server and isn't a moving target, once configured always the same BAK is used.
The customer is fine with this simple backup mechanism and also knows how to restore in case there are problems. So there is no real problem with that, but the owner was having the idea to rename TheDatabase.bak to TheDatabase.bak2 before doing the next backup.
I think there are ways to have two or more backups accumulating in the one bak file but I do agree in this simple mode and for the sake of having a fresh new file and a separate older one is not the worst idea, even though only putting the bak somewhere completely separate (eg cloud storage, overall file backup) would give an extra safety.
The software is used in a small office on only 2 stations, one of which "is" the server and so only there the frontend application can actually do such a file renaming at all. Unless I'd enable xp_cmdshell the client application could also command the SQL Server to do that from both clients.
I'm looking for other simple ideas for such a simple office setup. The database is small (~200MB) in comparison to what I work on for companies, with the big difference those have their DevOps and network and database admins to care for that, this customer hasn't.
Should I change the recovery mode to full, obviously? I don't see pressing reasons to do so. It would just enable a restore at any point in time, but daily backups are considered good enough.
I thought along the lines of simply giving the backups a date in the filename itself and the client directly at the server could delete the one that got two days old or keep weekly, monthly, quarterly, yearly backups in a grandfather-father-son rotation principle.
Bye, Olaf.
Olaf Doschke Software Engineering