SQL Assignment 2- Translation from Sandranese
--

Introduction
|
Video Ezy in Rockingham has been destroyed by a fire caused by improper cooking of pumpkin soup. The business’s computer system was backed up on a daily basis. However, all data was held on site in a non-fireproof safe.

Prepare a comprehensive report that covers all of the following issues:
•	A range of backup and restoration methods based on organisational and security requirements.
•	Full offline backups, completed to organisational and security requirements with minimal downtime.
•	Online file backups completed to organisational and security requirements with minimal downtime.
•	A database baseline with and without archiving, determined by organisational requirements.
•	Fully recovering the database to the baseline with minimal downtime.
•	Database recovery without loss of committed transactions, so there are no rollbacks.
•	Point of failure mitigation.
•	Creating or preparing a standby database to meet organisational requirements.
•	Implementing a standby database to support critical business functions.
•	Documenting a standby database.

	
Prepare a report to discuss what Video Ezy could/should have done.
|
Determine Database Backup methods
-list of backup/restoration methods (and ways to minimalise downtime), with brief description of each (with  advantages/disadvantages).
-in depth analysis of offsite storage scenarios (purpose, advantages, disadvantages).
-in depth analysis of onsite storage scenarios (purpose, advantages, disadvantages).
-other precautions. eg. removable hard-drives, fire proof storage, etc (purpose, advantages, disadvantages).
	
Determine Baseline and Recovery Procedures
-what could be the organisational and security requirements for a place like Video Ezy?
-plan possible baseline and recovery procedures for Video Ezy (with and without archiving).
-How could you recover a database in relation to the planned baseline?
	+with minimal downtime
	+without loss of transactions (no rollbacks)
	(ie. creating, preparing, implementing, and documenting a standby database)
-Discuss the points of failure in this plan, and how it fares to the old point of failure (better or worse?).
	
Conclusion
|
Out of everything you just explained, what would you recommend for Video Ezy?
--


List of backup/restoration methods:
|
Backup
-Full backup
-Copy backup
-Incremental backup
-Differential backup
-Transaction log backup
-File and filegroup backup

Recovery
-Simple (SQL Recovery Model)	
-Full (SQL Recovery Model)
-Bulk-logged (SQL Recovery Model)
--


List of things to take into account
|
What type of databases are being backed up?
	-Master database, crucial for everything but doesn't change too often once set up.
	-Other databases contain customers and monetary amounts and rental time that change often.
	-Video database.
	-Company staff and payroll databases also exist.

How important is the data in the database?
	-Databases containing monetary aspects are very important (this would be most databases).
	-The video database would also be very important.
	-Databases relating to rental time and returns wouldn't be as important as the video database since you can work out what videos are missing using that video database.
	-Company staff databases wouldn't be as important as the customer databases.

How often are changes made to the databases?
	-Rental and monetary databases would change the most.
	-Customer databases would change the second most.
	-The video database would only be changed when new/old videos are purchased/sold.
	-Staff database would only change with the staff- but relating databases like their payroll might change often.

How quickly do you need to recover the data?
	-Needs to be working during work hours at least.
	
What's the best time to schedual backups?
	-Off hours for mostly non-changing databases.
	-Slow databases like Videos and Staff can be backed up weekly.
	-Master database should be backed up whenenver it's changed.
	-Fast databases should be backed up continually if possible, or frequently (perhaps during non-peak hours), or at the least daily.
--


Bibliography
|
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
http://en.wikipedia.org/wiki/Backup#Lore
http://en.wikipedia.org/wiki/Off-site_Data_Protection
http://en.wikipedia.org/wiki/Disaster_Recovery#Preventions_against_data_loss
http://searchstorage.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid5_gci1248183,00.html
http://www.pcguide.com/care/bu/methodHard-c.html
--