Is using Maintenance plans a good idea for this?
-
Good morning,
We have 5 SQL Servers used by operations team.
QBDB01 - 62 Databases
QBDB02 - 41 Databases
QBDB03 - 39 Databases
QBDB04 - 31 Databases
QBDB05 - 28 Databases
The databases are mainly staging databases where data files are imported, scrubbed, merged etc.. It's usually one person working on the database at any one time.
The current backup process works like this:
1) All user databases set to Simple recovery model
2) A SQL job is run manually every two weeks to perform a full backup of all the databases except for master and tempdb
3) Another job which performs differential backups is run daily at midnight.
They've had this setup for a number of years now but I'm thinking of creating a maintenance plan on each SQL server to perform the backups instead. Would this be a good idea?
Any thoughts on this?
---------------------------------------------------------
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[/url]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 -
consider the number of DB and data loss acceptability before planning this.
defenitely go for better backup plan with or without maintanence plan.
take full back up daily if you have enough space and log backup every hour or two.
alternatively , try reducing deferential backup time.
Regards
Durai Nagarajan -
durai nagarajan (8/22/2012)
consider the number of DB and data loss acceptability before planning this.The databases are merely staging DBs used to perform data cleaning/merging etc.. HD space is not suficient to perform a full backup every day though.
defenitely go for better backup plan with or without maintanence plan.
Can you tell me what is wrong with the current setup?
Thanks.
---------------------------------------------------------
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[/url]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 -
Can you tell me what is wrong with the current setup?
what type of data available explain more clearly and what level of data loss is acceptable.
if the data is sensitive then you have to have full backup daily and tran backup hourly or so..
Regards
Durai Nagarajan -
durai nagarajan (8/22/2012)
Can you tell me what is wrong with the current setup?
what type of data available explain more clearly and what level of data loss is acceptable.
if the data is sensitive then you have to have full backup daily and tran backup hourly or so..
The databases contain staging tables so all the data already exists in other formats (.csv files, excel, xml ) etc... In the worst case, the data can be re-imported.
There are no front end apps using these databases. It's just users perfomring bulk updates/deleted and inserts.
Sorry if I'm not explaining this properly but I hope you get the idea.
It's just that I saw this article: http://www.sqlservercentral.com/articles/Backup+%2f+Restore/72234/ which got me thinking about using Maintenance plans rather than just T-SQL and jobs.
Thanks.
---------------------------------------------------------
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[/url]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 -
Maintenance plans are built in SSIS packages and T-SQL are sql code.
both of them has to use jobs to execute at certain intervals or time.
it is your option and nothing is worst.
for db backup if it is not critical(application not using it) continue with the current backup policy.
Regards
Durai Nagarajan -
durai nagarajan (8/22/2012)
Maintenance plans are built in SSIS packages and T-SQL are sql code.both of them has to use jobs to execute at certain intervals or time.
it is your option and nothing is worst.
for db backup if it is not critical(application not using it) continue with the current backup policy.
Thanks for the advice.
When I looked at this initially I also thought the current setup looks okay for the way they use the databases on these 5 SQL Servers.
But is the two week gap alright though? How to work out which is better like below?
---------------------------------------------------------
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[/url]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 -
Abu Dina (8/22/2012)
Good morning,We have 5 SQL Servers used by operations team.
QBDB01 - 62 Databases
QBDB02 - 41 Databases
QBDB03 - 39 Databases
QBDB04 - 31 Databases
QBDB05 - 28 Databases
The databases are mainly staging databases where data files are imported, scrubbed, merged etc.. It's usually one person working on the database at any one time.
The current backup process works like this:
1) All user databases set to Simple recovery model
2) A SQL job is run manually every two weeks to perform a full backup of all the databases except for master and tempdb
3) Another job which performs differential backups is run daily at midnight.
They've had this setup for a number of years now but I'm thinking of creating a maintenance plan on each SQL server to perform the backups instead. Would this be a good idea?
Any thoughts on this?
Why manually? Why master is not included?
-
in the current scenario you may have max 1 day data loss, if the DB crashes before defferential.
if it is ok , you have defferential to restore back.
if you cant have max 1 day data loss try reducing defferential backup interval.
Regards
Durai Nagarajan -
clayman (8/22/2012)
Abu Dina (8/22/2012)
Good morning,We have 5 SQL Servers used by operations team.
QBDB01 - 62 Databases
QBDB02 - 41 Databases
QBDB03 - 39 Databases
QBDB04 - 31 Databases
QBDB05 - 28 Databases
The databases are mainly staging databases where data files are imported, scrubbed, merged etc.. It's usually one person working on the database at any one time.
The current backup process works like this:
1) All user databases set to Simple recovery model
2) A SQL job is run manually every two weeks to perform a full backup of all the databases except for master and tempdb
3) Another job which performs differential backups is run daily at midnight.
They've had this setup for a number of years now but I'm thinking of creating a maintenance plan on each SQL server to perform the backups instead. Would this be a good idea?
Any thoughts on this?
Why manually? Why master is not included?
No idea... the job is setup but isn't scheduled. Easy enough to fix though.
Not sure why master is not backed up. But I will also add this to my list of improvements to the current backup process.
---------------------------------------------------------
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[/url]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 -
clayman (8/22/2012)[hrWhy manually? Why master is not included?
great, i missed it, try to included clayman ideas as well.
Regards
Durai Nagarajan -
Hiya,
You say these are staging tables...
Couple of questions then:
1.) Do these tables get cleared down as part of the staging process?
2.) If the above is true how often does this staging process happen in a day?
2.) How big are your differencial backups compared to the Full Backups?
3.) In the event you have to restore these databases how quickly do you need them back online?
-
Paul Clark-418949 (8/23/2012)
Hiya,You say these are staging tables...
Couple of questions then:
1.) Do these tables get cleared down as part of the staging process?
2.) If the above is true how often does this staging process happen in a day?
2.) How big are your differencial backups compared to the Full Backups?
3.) In the event you have to restore these databases how quickly do you need them back online?
Hi Paul,
Thanks for your comment!
Here are my answers to your questions.
1.) Do these tables get cleared down as part of the staging process?
No I'm afraid not. What happens is a member in the Operations team will get a new job (e.g data file to be cleansed and deduped). If the job is for an existing customer then a database should already eixst for them so the new data file is imported, cleaned, deduped, merged etc then gets exported. From what I've seen from the scripts used by these guys, I haven't seen anything that drops these working tables. In fact during my first week in this job (last month) one of the SQL Server machines had ran out of disk space. When I investgated it I found a DB that hadn't been touch in over 18 months that was 50GB in size!
2.) If the above is true how often does this staging process happen in a day?
We have 7 users working in the data services team. Users could work on several small jobs in a day.
2.) How big are your differencial backups compared to the Full Backups?
Not sure about size but here is a snapshot of the time it takes for the differential backups job to run (backs up 58 user databases). It takes just 18 seconds to the DIFFS the day after the complete backup job. By the 14th day it's still under 1 hour.
In the event you have to restore these databases how quickly do you need them back online?
Good question and the answer is as quickly as possible I imagine!
As you can see from the above, even after 13 days following a full backup, the differential backup jobs takes less than 1 hour to backup 50+ databases so not much changes sometimes. I can't imagine these files being too large to be honest.
Thanks.
---------------------------------------------------------
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[/url]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 -
Hi Abu,
Okay then, the reason I asked the fourth question was to answer your question as to whether a 2 weekly full backup is adequate.
If you have a scenario where you need to restore any of the databases the day before a Full Backup then this will take alot longer than then say the day after the full backup. This is because it has to read each differencial backup upto when you want the database restored to...
Do you have a SAN or NAS in your environment with snapshot technology?
Our DB Backups go to a NAS Share that has snapshots turned on...
The snapshots on the volume are taken nightly and kept for 1 month. I then take nightly full backups (with log backups every 15 minutes for those DBs that are in Full Recovery).
In this way I can take Full Backups nightly but still go back to a snapshot copy from upto a month previous.
In your case as you are doing a Full Backup bi-weekly you could keep the snapshots for 2 weeks...
This also means that your backups are not local to the server so in the event that the server goes bang, you still have your SQL Backups.
The other questions are really irrelevant, but the reason I asked was that if the tables were cleared down by the staging process then your diff backups would probably not be much smaller than a full backup...
I would also advise that you also set up backup schedules for all your system DBs as well, whether using transact or SSIS..
Hope the above makes sense...
-
PS to the above...
For getting backup sizes:
SELECT [database_name]
,[recovery_model]
,[backup_start_date]
,[backup_finish_date]
,[type]
,[backup_size]
,[compressed_backup_size]
FROM [msdb].[dbo].[backupset]
ORDER BY [database_name], [backup_start_date]
The types codes are as follows:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
I've edited this as I was being lazy, see below:
SELECT [database_name]
,[recovery_model]
,[backup_start_date]
,[backup_finish_date]
,CASE [type]
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential DB Backup'
WHEN 'L' THEN 'Log Backup'
WHEN 'F' THEN 'File or Filegroup Backup'
WHEN 'G' THEN 'Differential File Backup'
WHEN 'P' THEN 'Partial Backup'
WHEN 'Q' THEN 'Differential Partial Backup'
END AS [Backup Type]
,[backup_size]
,[compressed_backup_size]
FROM [msdb].[dbo].[backupset]
ORDER BY [database_name], [backup_start_date]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply