How Often Should I Backup my Database - Query Answered

"In recent time, some of our counterpart organizations have faced database corruption. Though our company takes backup every week, we have decided to become more careful. I would like to pose a question to the expert admins here: How often should I backup my database? Our company is a mid-sized organization and our database size is also moderate. Please suggest us some good backup policies."

The value of data for any organization is beyond description, yet, people often neglect its importance. That is why, the cost of data loss in US alone amounts more than $10 billion every year. Sometimes, data loss is not preventable; but a recent and complete back of the database can save us from losing the data for good. In order to save money, time, reputation and most importantly data itself, every company should backup their database regularly. Now the question arises regarding the frequency of database back. Many people want to know, "how often should I backup my database?" In this post, this question will be answered as we will discuss how oftem you must take backup of SQL Database.

How Often Should You Backup SQL Database?

The frequency actually depends on several things. Database size, how busy it is, the criticality of data, all play vital role to determine how often the backup to be taken. Usually the backup process is the mixture of complete backup, incremental backup (differential), and transaction log backup. There are different types of backup frequency policies that organizations can adapt according to their need.

1. Weekly: According to this backup policy, complete database backup is taken every week or 168 hours. Besides, the differential backup is done every day. This type of backup is usually chosen for databases that have only a few transactions every week or contain less important data. A good example can be the payroll database of a tiny business company where very little changes are done usually and any lost data can be recreated easily.
2. Daily Once: This is a simple backup policy where the complete backup is done every 24 hours without any differential backup. This policy is ideal for small companies with more transactions than the company mentioned in the earlier point. Usually, companies that issue manual receipts need this type of backup. As there are manual records, any lost data can be recreated with its help.
3. Daily Four Times: This database backup policy is ideal for any particular database of a mid-sized organization or a bank. These databases go through many changes every day. For this reason, this policy supports complete database backup every 24 hours and incremental backup every 6 hours.
4. Daily Six Times: This backup policy can be termed as the through backup. As per this policy, complete backup is done once every 24 hours, differential backup at every 4 hours and transaction log backup every 1 hour. This policy is for the organizations that will face disastrous consequences or interruption in business functionality if any data loss occurs. Mid-sized retailers and large enterprises usually adopt this policy.
5. Daily Eight Times: This policy has been designed for the database that is busy and contains business-critical data. As this system cannot afford to lose any transaction, transaction log backup is done every 15 minutes. Besides this policy includes incremental backup every 3 hours and full backup every 24 hours. Large online retailers, commercial bank, and hospitals are the main subscribers of this policy.

Quick Way to Restore Database Backup

The aim of database backup is to restore it when necessary. Due to any bug, the database may face issue any time. In case of major database corruptions, it is often impossible to access the database. Apart from these, any natural disaster can also cause damage to the database. In such cases, recent backup can help organizations to overcome the adverse situation. Users can take the help of SQL Backup Repair tool to restore the backup. This tool is an efficient one to restore and export .bak files into SQL database. It supports 2014, 2012, 2008 and all other versions of SQL Server database. File size is not an issue for this program as it can export BAK file of any size to any SQL Server version. This application restores the backup file seamlessly and helps to resume business operation.

Conclusion

In this post, we have answered a common query: “How often should I backup my database?" From the above discussion, we have learned that the frequency of the database backup completely depends on factors like its size, importance, etc. Users can read the types of backup policy and decide which one to choose for their organization.

Read More

How to Read BLOB data in SQLite in an Efficient Way

SQLite is one of the most commonly used database engine. Its source code is available in public domain which can be used for commercial or private purpose. Common data types used in SQLite are NULL, INTEGER, REAL, TEXT and BLOB. Unlike Blob, all other data types stores a particular type of data. Whereas BLOB data type stores binary data, which are typically images, videos, audio or even binary executable codes. This datatype is not only deals with SQLite, but also supported by most of other databases.

Why BLOB is so Important?

As talked above, BLOB data type contains binary data, which is not in readable form. Any type of data which is in binary form appears similar for human eyes. Even database managers, do not have idea what the blob data contains and how to deal with. From a forensic investigators point of view, these BLOB data contains crucial evidence regarding digital crime. The reason behind this is that most of the web browsers, Android devices, etc. uses SQLite files to store data.

Structure of BLOB Data

Each BLOB data contains two fields namely Type and Data.
create table table_name (..., Blob_type, Blob_data)
Each column in a Blob type are meant for different data. Below describes each column name and the type of data it holds.

• Text: Holds plain text of data. This excludes NULL values from the database.
• Data: Specially designed to hold encoded binary data contents. Here,it is used to represent NULL values, whereas %25 is to present a percent symbol.
• File: This file contains the filename that points to a particular file on disk.
• BLOB: This is a reference to a separate BLOB table in the database.

How to View Data from BLOB?

Since BLOB contains binary data, the viewing and analyzing of BLOB data is very difficult for human to understand. Simple SQL queries can be used to view and analyze BLOB data contents. Let’s see how it is possible.
Using Command line interface of SQLite:
Creating a table of BLOB type:
sqlite> create table blob_test (b_id blob);
sqlite> insert into blob_test values (x'01234234566789abcjghsjddef0123456789abcdef');
Viewing BLOB contents using SELECT query:
sqlite> select * from blob_test;

☺#Egë½═∩☺#Egë½═∩

sqlite> select ''||b_id from blob_test;

☺#Egë½═∩☺#Egë½═∩

Here, you can see that the contents are not in human readable format. SQLite provide another Query ‘quote’ to view the contents in Hex format. Lets see how this query works.
select quote(b_id) from blob_test;
This will give you result as follows:
x'01234234566789abcjghsjddef0123456789abcdef'
This is not a reliable method to view and analyze data from BLOB as it is not in a human readable format. Moreover, the investigator should be an expert in database management. To overcome this limitation investigator can use a smart tool to view and analyze SQLite BLOB data contents.

Suggested Solution- SQLite Forensics

Explore & Extract SQLite Database files using SQLite Forensic Tool.

Prominent Features of SQLite Forensics tool

Support for BLOB Data Type:
SQLite Forensic tool allows users to preview and analyze tables, structures, byte code, and the multimedia contents from BLOB data type.
Recovers Deleted Records:
The tool is capable to recover the deleted contents from the SQLite files. these deleted items are listed in separate tab so that investigator can analyze these contents easily.
Multiple Views of DB Contents:
User can view and analyze contents form the SQLite database in tabular form or in hexadecimal form. This helps the investigator in deeply analyzing the database file.
Recover Associated Journal Files:
The tool is capable to recover the associated journal files related to SQLite file. This is the backup file for the original data file so these can be the major residence of digital evidences.
Export contents to multiple formats:
The recovered artifacts can be exported and saved in different file formats. Various exporting formats supported by the tool are CSV, PDF and HTML. This enables user to access the collected artifacts in various platforms.

Conclusion

BLOB data type contains binary data. The blog discusses the manual approach to read BLOB data type but as it is not in human readable form,the blog covers quick solution to read and analyze the SQLite BLOB data type.

Read More

Understanding the Role of SQL Server Agent in SQL Server

SQL Server Agent is a component of Microsoft SQL Server that is responsible to execute & schedule tasks or jobs in SQL Server. It runs as a Windows service and starts automatically when the system boots. It basically performs the backup task, handles reporting services, log shipping, and user tasks like; T-SQL scheduling, etc. It also supports operators and alerts for administrator notification.

To store information, SQL Server Agent uses SQL Server to perform a Job and each Job contains one or more tasks to be performed which is assigned to every job step.

For example, backing up a database.

It runs a job on schedule according to a specific event and on demand. Let’s take an example, if you backup database of all servers and schedule the backup for specific time duration, then an error or notification may encounter while taking backup.

Components of SQL Server Agent:

The SQL Server agent uses the following types of components to perform its tasks.It defines the tasks that is to be performed and when to perform it.

Jobs

Job is performed on particular tasks that can be run multiple times. It can run on multiple remote servers and one local server.

A user can run jobs in different ways like; based on one or more schedules, according to one or more alerts and by executing sp_start_job stored procedure.

Job step is an action in a job. The Job step might be a Transact-SQL statement, Analysis Services server commands and executing SSIS package. Each step in a job runs on security context. For transact-SQL it uses EXECUTE AS statement and for others, it uses proxy accounts.

Schedules

It specifies when a job runs, more than one job can run on the same schedule and vice versa. It can be defined on a job if SQL Server Agent starts, specific date or time at one time, CPU utilization of computer at idle level and at the time of recurring scheduling.

Alerts

An alert is an automatic reaction to a specific event. An alert can occur during one of the following conditions:

• SQL Server performance conditions

• SQL Server events

• WMI (Microsoft Windows Management Instrumentation) events on the computer where SQL agent is running. 

An alert can perform notification to one or more operators and it runs a job.

Operators

The operator defines contact information of individuals for the maintenance of single or multiple instances of SQL Server. It does not contain security information. SQL Server notifies operators for one of the following:

• Pager 

• Email

• Net send

Proxies

The security context is managed by the SQL Server agent through proxies. It can be used in more than one job steps. Proxies can be created by sysadmin fixed server roles. The members of fixed server roles have unrestricted access to the proxies and only SQLAgentUserRole, SQLAgentOperatorRole and SQLAgentReaderRole can use proxies which are assigned to them.

Conclusion

The blog explains about SQL Server agent, their roles, components (Jobs, Schedules, Operators, Alerts, and Proxies, etc.) and how they are carried out in the SQL Server. The SQL Server Agent is the most important part of SQL Server, which specifies each and every task.

Read More

Microsoft SQL Server Analysis Services and Its Synchronization

SQL Server Analysis Services (SAAS) serves a broader view of data for analysis so as to make things simpler for business analysts. Implementation of SAAS gives an opportunity to users to ask compound query and convert it into actionable insight where information for better business decision making will be available. Instead of just collecting raw database of large size, the Business Intelligence (BI) methods ensure that first the data is converted into actionable intelligence.

Composition of SQL Server:

SQL Server Analysis Service (SAAS): This helps in Online Analytical Processing (OLAP) that comprises of data mining solutions. For understanding tends, patterns, specialized algorithms are used.

SQL Server Reporting Service (SSRS): This gives a solution to create, publish, and distribute business reports to the users.

SQL Server Integration Services (SSIS): This performs ETL operations (Extract, Transform, and Load). Means, the data can be extracted from source, manipulated as per requirement, and it import to the recipient data.

Talking about the components of Business Intelligence, it’s only SQL Server Analysis Service. The worth highlighting fact about SSAS is it OLAP instead of OLTP!

Online Transaction Processing (OLTP): OLTP database is frequently updated with statements like (INSERT, DETELE, UPDATE). Its basic features include:

·         Short Transactions

·         Simple Queries

·         Small portions of data involved

·         Frequent Updates

    Online Analytical Processing (OLAP): OLAP type database is queried using the SELECT statement. Its general attributes include:

·         Long Transactions

·         Complex Queries

·         Large portion of data involved

·         Less Frequent updates

Synchronizing Analysis Services using SQL Server Management Studio

In case of SSAS, synchronization can simply be considered as replicating database of System Analysis from one running instance to another. Database administrators generally keep a copy of database on test Server and then restore them to production Server. Synchronization is one of the finest techniques that can be adopted in order to ensure that all SAAS instances have same data. Here is how to proceed:

Step1) Launch SSMS and move to “Databases” folder. Right click on it and choose “Synchronize”.

Step2) A windows named “Synchronize Database Wizard”, type source Server and source database name. Similarly, provide credentials for destination database and Server.

Step3) a dialog box will appear on screen that will give the service to change location of the local partition. Utilize this option according to your requirement.

Step4) Provide security synchronization. Choose between any of the three security options. Click on “Finish” button to start synchronization.

Conclusion:

In the above description I have explained different services of SQL Server and synchronization with SQL Server analysis services. Step by step procedure of synchronization is briefly explained through which users will be able to understand the actual fact behind synchronization process. Further more details will be given to your in next blog.

Read More