I have the following setup:1) 5 SQL Server instances (within the trusted network)
DBSERVER01 – 62 DBs (58 SIMPLE, 4 FULL)
DBSERVER02 – 41 DBs (41 SIMPLE)
DBSERVER03 – 39 DBs (39 SIMPLE)
DBSERVER04 – 31 DBs (30 SIMPLE, 1 FULL)
DBSERVER05 – 28 DBs (16 SIMPLE, 12 FULL)2) 3 SQL Server instances (in the DMZ)
DBSERVER06 – 23 DBs (13 SIMPLE, 10 FULL)
DBSERVER07 – 14 DBs (11 SIMPLE, 3 FULL)
DBSERVER08 – 11 DBs (6 SIMPLE, 5 FULL)Current backup process:
There exists two SQL Jobs on each SQL Server instance.
1) FULL Backup – this job is manually started every fortnight by the IT Manager. This executes a stored procedure which loops through all databases (except for Master and tempdb).
2) Differential Backup – this job is scheduled to run every evening and makes a differential back of all databases except for (master and tempdb)DBSERVER01 to DBSERVER05
These hold staging databases used to load source files and various updates and inserts. There are no applications that use these databases and it’s normally one user working on one database.DBSERVER06 to DBSERVER08
These hold databases used by Single Customer View applications. These are accessed via a web front end by our customers. Some of the databases are used purely for reporting purposes but some customers do make some changes to the data (maybe 20-30 changes per day).
It’s a simple setup but now I have been asked to come up with a new backup plan and also automate the backup process and generate some reporting mechanism.My approach to solving this problem:
This is what I am planning to include in my proposal to management. If anyone can comment then I’d be eternally grateful!
1) For DBSERVER01-05 we need to establish the appropriate recovery model for all databases. My guess is that the dozen databases in FULL recovery model were created that way because model is set to FULL and this option wasn’t updated after the DBs were created.
2) For DBSERVER06-DBSERVER08 we need to look at each customer database and establish the appropriate recovery model based on the below:
How much data can be lost? A day, one hour, a week, none
a) If less than a day or none then FULL
b) If more than a day then SIMPLE
What kind of processing occurs? Transaction, batch load, reporting only, a combination
a) If transaction or a combination then FULL.
b) If Reporting then SIMPLE
This data be easily recreated if there is a failure or it is not the only source of this data?
a) If No than FULL
b) If Yes then SIMPLE
The backup plan for DBSERVER01 – DBSERVER05
seems reasonable to me but just need to include masterdb in the backup list and schedule the FULL backup to run at a certain time every two weeks rather than have the IT manager trigger the job on each server every two weeks!
The backup plan for DBSERVER06 – DBSERVER08
can remain the same for databases in SIMPLE recovery model (if that’s what the appropriate recovery model is for the customer) but for databases in FULL recovery model then a transaction log backup every three times a day maybe sufficient.
The next stage is reporting on Backup across all servers.
For this I am planning to create a centralised database which will house information about the back status of all databases on all servers. I plan to have a job that runs say 7AM every day which would look for all backups completed in the last 24 hours on each server and store the information in this central database. For this I am going to create an SSIS package and schedule a job to run it.
I will then use SSRS to generate the required reports on a daily/weekly/monthly basis as needed.
Can someone let me know if my proposal sounds reasonable or am I missing something important here?
Thanks in advance.
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda. David Edwards - Media lens
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.Howard Zinn