Question

Database Back Up & Recovery project According to the Cellitech backup and recovery requirements, the system should be backed up regularly. Recovery procedures should also be documented for use when data needs to be recovered. List the steps for making a full backup. Then document the steps for performing a full recovery. Although making a backup prior to recovery can consume valuable time, it may be needed if the recovery fails. Include the screen shots for each step in the procedures. Please indicate at least one issue that you came across during the documentation process and explain how you overcome the problem. ((db to be backedup is in phpMyAdmin and connected to Access via MySQLWorkbench))

Answer 1

Why we use data backup and recover.. Example
To minimize the effects of unplanned downtime and to maximize data availability and recover ability, smart IT organizations
must create, implement and maintain a BRMP. A BRMP provides a framework for understanding the backup environment, a vehicle for documenting the standard procedures to be followed for backup and restore operations and a repository for the corporate best practices and backup policy definitions that have been implemented. Here's how to create a BRMP plan along with tips for best practices.
Staffing requirements: The BRMP should spell out staffing needs. What's required depends on several factors including backup schedules, backup windows and service level agreements with customers. Many large enterprises will require some form of coverage on a 24X7 basis. A 24X7 coverage model requires a minimum of seven to 10 people to adequately staff all shifts throughout the week, including management support.

Define operational procedures: The management plan must contain the procedures for monitoring the backup infrastructure, for ensuring successful backup and recovery job completion, for complying with the change management process and for testing the restore process.
What should be monitored?
All components of the backup infrastructure must be monitored to quickly identify and resolve any problems that will surface. These components include backup and restore job status, backup servers and clients, automated libraries, LANs,
network-attached storage (NAS), storage area networks (SANs), backup networks and the storage itself. Most of these components may be monitored via in-band and out-of-band communication methods such as real-time backup and restore activity monitors, error and event logs and SNMP traps aggregated up to enterprise-level frameworks.

What actions should be taken in the event of a backup or restore failure?
What information must be captured to facilitate root cause analysis? When should the backup be restarted? These operational processes
must be documented and tested. The process should also contain a technical and business escalation procedure defining whom to contact at the appropriate time.

challenges in backup and recovery:
Today's complex environments demand highly skilled IT professionals to ensure the backup solution is working as designed. Unfortunately, managing the backup and recovery environment is a job no one really wants. It can be a thankless job with high expectations for success and no tolerance for failure. A general perception among administrators is that no one has ever been promoted for ensuring successful backups. And, sorry to say, the opposite is all too true: Jobs have been lost as the result of unsuccessful backups.

steps to backup and restore

Step 1: Understand the backup environment- Before a successful BRMP can be created, it's important to conduct a thorough assessment and inventory of the existing backup environment, including backup servers and clients, automated libraries, backup media and storage networking components.
Step 2: Perform capacity planning- nce the assessment and inventory are completed and the backup infrastructure is understood and documented, the next step is to perform capacity planning. The purpose of capacity planning is to identify the sources of storage growth and perform a gap analysis to determine the differences between the current infrastructure capabilities vs. expected requirements.
Step 3: Analyze current policies and procedures- The foundation of a successful BRMP is the documentation of policies and operational procedures. In this step, internal and external customer requirements for backup and recovery must be reviewed and documented.
Step 4: Determine resource constraints- In an ideal world, an enterprise would have unlimited resources to accomplish their business objectives - including ensuring a successful backup and recovery. Unfortunately, this isn't the case. A realistic BRMP will take into account the business constraints most organizations face. Key resource areas that must be reviewed include personnel constraints, physical infrastructure constraints and financial constraints.
Step 5: Create a BRMP- At this point, there will be a wealth of information available to provide a baseline for the management plan. This includes information about the existing backup infrastructure, requirements for storage growth, backup policies and procedures and resource constraints. The last step before actually writing the plan is to define staffing requirements, operational procedures and the backup and media management policies.No doubt, this is a formidable task that can take months. And, of course, most users want all of their data backed up and retained indefinitely, or the legal department usually wants a limited amount of backup data and short retention periods. A good management plan should also reflect the disparate requirements. Reality and consensus lies somewhere in the middle.
Step 6: Implement the plan- Once the BRMP is completed and approved, it's time to implement the plan. Take a phased approach to implementation. First, hire and train the required operational staff or select an outsourcing vendor. Second, acquire and install any of the backup hardware and software identified in the capacity planning phase. Next, implement and test the operational procedures and backup policies in a controlled environment to avoid impacting production backups. This is also the time to implement and test any new backup management software tools. Be prepared to make some adjustments to the plan as required. After testing is complete, you should schedule a full roll out of the policies and procedures across the enterprise. Consider using a professional project planning software package when implementing the BRMP. Don't make the same mistakes other organizations havemade - assuming that just because the project is approved and paid for, it will be properly implemented. Be proactive: Follow theproject plan and stay on schedule.

Step 7: Monitor the management plan
Obviously, a company's business changes - in some cases, on almost a daily basis. New applications drive revenue and profit growth.And of course, the storage environment continues to grow at an exponential rate. Due to these ever-changing requirements, it's important to continuously monitor the backup and recovery management plan to ensure its meeting the business and data protection needs of the enterprise.

SQL Server Backup Categories and Types:

Database backups – copy into a backup file the data and objects in the primary
data file and any secondary data files.
•Full database backup – backs up all the data and objects in the data file(s) for a given database.

Full Database backup images:

EXEC madb.dbo. ap. delete_database backuphistory database nameN DatabaseName : GO USE [master] : GO DROP DATABASE DatabaseName : GO

ASQLQuery3.sql-.UPlsmcgehee (56) Connect,髺製·TES .(SQL Server 10.0.2531 SQLBACKUPismcgehee) System Databases Database Snapshots Database New Database... New Query Script Database as» Tasks Policies Facets Start PowerShell Reports Rename Delete Refresh Security 困 Server。 Replication Detach.. Take Öffline Bring Online Shrink Back Up... Restore Mirror Launch Database Mirroring Monitor... Ship Transaction Logs.. Generate Scripts.. 田島SQL Server using Web Service. Import Data. Export Data. Copy Database... Manage Database Encryption...

Back Up Database- DatabaseForFullBackups Select a page Scipt Options Source Recovery model Backup type Ful Copy Only Backup Backup component o Database Fles and flegroups Backup se! Name Backup set will expire 8/ 5/2010 Server. Back up to o Disk Tape Add Remove Contents Ready

Restoring full backup images

Connect ▼ 髺習. T S . (SQL Server 10.0.2531-SQLBACKUPlsmcgehee) Databases 田 System Databases Database Snapshots DatabaseForFullBackups 田 Security Server Objects Replication Management 田島SQL Server Agent

B-SQL Server 10.0.2531- SQLBACKUPlsmcgehee) Databases System Databases a Database Snapshots SecunityNew Databese... aRepication Script Database as . E 勗SQL Server11 'as Security Server Obj New Query Managem Tasks Policies Facets Start PowerShell Reports Rename Delete Refresh Properties Detach.. Take Offline Bring Online Shrink Back Up Restore Mirror. Launch Database Mirroring Monitor.. Ship Transaction Logs... Database... Files and Filegroups Transaction Log... lGenaerte Scipe

The restored database screen:

Scipt Help Destination for restore Select or type the name of a new or existing database for your restore operation. To database: To a point in time: DatabaseForFull Backups Most recent possible Source for restore Specify the source and location of backup sets to restore. From database. Database ForFullBackups From device: Select the backup sets to restore: Restore Name Component Type Server Database Position First LSN 1 22000002488

Configuration for full restore

Source for restore Specfy the source and location of backup sets to restore From database: From device: DatabaseForFullBackups Select the backup sets to restore: Restore Name Database DatabaseForFul DatabaseForFull Backups-Full Database Backup Database Full Back

Page config for full database store

回 rennte the existing database (WITH REPLACE) Preserve the replication settings (WITH KEEP_REPLICATION) Prompt before restoring each backup Restrigt access to the restored database (WITH RESTRICTED_USER Regtore the database files as: Fle Type Rows DataCSQLDataDatabase Forfull Backups d Log Oniginal File Name Restore As DatabaseForfullBackups log CSQLData Database ForFullBackups log ld Recovery state restored.(RESTORE WITH RECOVERY Legve the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored (RESTORE WITH NORECOVERY Leaye the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reversed (RESTORE WITH STANDBY) Standby file

•Differential database backup – backs up any data and objects in data file(s) for a given database that have changed since the last full backup.
•Transaction log backups – copy into a backup file all the log records inserted into the transaction log LDF file since the last transaction log backup.
•File backups – copy into a backup file the data and objects in a data file or filegroup.
•Full file backup – backs up all the data and objects in the specified data files or filegroup.
•Differential file backup – backs up the data and objects in the specified data files or filegroup that have changed since the last full file backup.
•Partial backup – backs up the complete writable portion of the database, excluding
any read-only files/filegroups (unless specifically included).
•Differential partial backup – backs up the data and objects that have changed since the last partial backup.
In my experience as a DBA, it is rare for a database to be subject to file backups, and some DBAs never work with a database that requires them.

Issue--fix Recovery Pending State in SQL Server Database

Manual way to fix “SQL server recovery pending” state:
There are 2 manual ways to start Recovering Pending State in SQL Server database that has been marked in recovery pending state:

Solution 1: Mark database in Emergency mode and initiate forceful Repair

Execute the following set of queries
ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [DBName] set multi_user
GO
EMERGENCY mode marks the database as READ_ONLY, disables logging, and grants access only to system administrators.
This should resolve any corruption and bring the database online. The database will come out of EMERGENCY mode automatically.

Solution 2: Mark database in Emergency mode, detach the main database and re-attach it

Execute the following set of queries:
ALTER DATABASE [DBName] SET EMERGENCY;
ALTER DATABASE [DBName] set multi_user
EXEC sp_detach_db ‘[DBName]’
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’

These commands will cause the server to get rid of the corrupt log and build a new one automatically.
Note: Before initiating any of these repair procedures, you should ensure that you have proper backups of the database in question. This is to have a fail-safe copy in case anything goes wrong. Also, remember that these are highly technical procedures and you shouldn’t be performing them if you are unsure or do not have proper technical expertise.