Sunday, March 12, 2017

T-SQL script to get Backup Details of a Database and the Sequence it was taken

Here is an handy script which give the details of the list of backup(s) taken of a database and the sequence it was taken.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = '<DB Name>'

 BS.server_name AS [Server Name]
,BS.database_name AS [Database Name]
,BS.recovery_model AS [Recovery Model]
,BMF.physical_device_name [Location]
,(CAST(BS.backup_size / 1000000 AS INT)) AS [Size of Backup (MB)]
,CASE BS.[type] WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END AS [Type of Backup]
,BS.backup_start_date AS [Backup Date]
,BS.first_lsn AS [First LSN]
,BS.last_lsn AS [Last LSN]
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE BS.database_name = @db_name
ORDER BY backup_start_date DESC , backup_finish_date

Monday, January 16, 2017

Kernel for SQL Database - Product Review - A guest Post by Andre Williams

Most of the organizations that handle large amount of data is very much dependent on MS SQL Server for their operations. However, an MS SQL database corruption, can cause a lot of troubles to the organization owing to the criticality of the data contained in the database. Fortunately, many SQL Server recovery tools are available in the market. We will try to understand the features, working, and benefits & drawbacks of a popular, SQL recovery tool—the Kernel for SQL Database.

Product information
  • Name - Kernel for SQL Database
  • Description – MDF file recovery software (recovers SQL data from corrupt databases)
  • Developers – Lepide Software
Company information 
Lepide Software, the developers of Kernel range of products, is known for sophisticated IT security, management, and recovery solutions. Its Kernel range, designed and developed for small and mid-sized organizations offers cost-effective solutions for most of the day-to-day issues faced by IT organizations. These products help organizations in email migration, email recovery, data recovery, file repair, and more. The company has a loyal customer-base spanning across the world owing to its quality products and dedicated customer support.

Notable features of the software
  • Automatically detects the SQL version of the database
  • Fixes almost all types of SQL corruption issues
  • Recovers all type of SQL data—tables, triggers, views, stored procedures, constraints, indexes, rules, and user defined functions/data types
  • Works even with large SQL databases
  • Recovers data even when the SQL Server is live
  • Supports MS SQL 2000, 2005 and 2008, 2008R2 and 2012 versions
Kernel for SQL Database - versions available
  • Demo version
    The demo version, meant educate users on SQL recovery process, is completely free. But it is not equipped with the saving facility.
  • Licensed version
    Licensed version is the fully featured version of the software and is available on purchase only. It can save all the recovered data.
How to use Kernel for SQL Database to repair corrupt MDF files?
The hallmark of Kernel for SQL Database is the simplicity of its operation. The steps are simple, the interface is intuitive, and recovery is quick. The major steps of the SQL database recovery process are outlined here:
  1. Selecting MDF file for recovery
    The Open button (in the main page of the software) opens the Select SQL Database dialogue box. In this dialogue box, one can browse for the corrupt database. If users are sure about the SQL server version, the can select the version. Otherwise, they can use the auto detect option. Finally, the recovery process can be initiated by clicking the Recover button.
  2. Previewing the recovered data
    Once the software displays the recovered objects, the users can click an object on the left tree to preview its data.
  3. Saving the recovered data
    Two saving options are available—saving to SQL Server and saving to batch file. The first option allows to save directly to an SQL Server either through server authentication or through Windows authentication. In the Batch File option, the data is stored in script files in the desired location (which can be copied to a new database).
Benefits and drawbacks of Kernel for SQL Database
The important benefits of Kernel for SQL Database are:
  • Friendly interface
    The software interface itself provides information on how to execute the recovery process. So even non-technical users can perform recovery without any help. 
  • Preview facility
    Everything recovered can be viewed on the software’s preview pane before saving. Previewing helps users to know about the recoverability of the data.
  • Automatic detection of SQL Version
    If the users know the version of the SQL, they can select it. But if they do not know, the software can automatically detect the SQL version.
  • Multiple saving options
    The software saves the recovered MDF file files to SQL Server directly. In addition, it can save files in batch file, which users can copy to a new database when required.

The major drawback of Kernel for SQL Database is:
  • No saving option in the trial version
    The trial version of Kernel for SQL Database cannot save the data. With this version, users can only preview the data. 

Final verdict
Kernel for SQL Database is a great tool for SQL recovery. As we understand, users are happy with the product. Still the product can be improved in some areas. Also, it is good if Lepide can add some limited saving facility to the trial version. 

Performance rating
Overall rating for the software is 4/5

About Andre Williams
Andre Williams, with more than three year's experience in SQL related technologies, contributes articles, blogs, and how-to tips regularly.

Social Media Profiles

Sunday, October 30, 2016

Using DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2

DBCC CLONEDATABASE is a new DBCC command introduced in SQL Server 2014 SP 2 which is used for creating the clone of a specified user database which helps in troubleshooting the performance issues related to the query optimizer.

When a clone of the database is created using DBCC CLONEDATABASE, it will create a schema and statistics only copy of the specified database and does not contain any copy of the data.

Creating the clone is as simple as passing the source database name and clone database name to the DBCC command.

Output of the DBCC Command

Once the cloning is completed, the cloned database will be in Read-Only mode.

SELECT name, database_id, is_read_only  
FROM sys.databases 
WHERE name in ('SansSQL', 'SansSQL_Clone') 

So what actually happens when we issue the DBCC CLONEDATABASE command on a database?
It will start with few validations before the clone is created, The following validations are performed by DBCC CLONEDATABASE. The command fails if any of the validations fail.
  • The source database must be a user database. Cloning of system databases (master, model, msdb, tempdb, distribution database etc.) isn't allowed.
  • The source database must be online or readable.
  • A database that uses the same name as the clone database must not already exist.
  • The command isn't in a user transaction.
If all the validations succeed, DBCC CLONEDATABASE will do the following operations:
  • Creating primary data file and log file
  • Adding secondary dataspaces
  • Adding secondary files
The destination database files will inherit the size and growth settings from the model database and the file names of the destination database will follow the source_file_name _underscore_random_number convention. 

SELECT database_id, file_id, type_desc, name, physical_name 
FROM sys.master_files 
WHERE DB_NAME(database_id) in ('SansSQL', 'SansSQL_Clone') 

Then the DBCC CLONEDATABASE will do a Internal Database Snapshot with the following steps
  • Validate the source database
  • Get S lock for the source database
  • Create snapshot of the source database
  • Create a clone database (this is an empty database which inherits from model)
  • Get X lock for the clone database
  • Copy the metadata to the clone database
  • Release all DB locks
Using the below command, we can check if a database is a clone or a normal database.
      ,DATABASEPROPERTYEX('SansSQL_Clone','isClone') AS SansSQL_CloneDB_CloneStatus


Wednesday, October 5, 2016

SQL Backup Recovery Tool to Repair Damaged SQL Backup - Product Review - A guest Post by Daniel Jones

It has been commonly observed that many users are inclined to store the important and crucial data in SQL Server due to its reliable and consistent data storage feature. All the data is stored efficiently in data files i.e. MDF and NDF files. Unfavourable situations like server breakdown, system corruption etc can arise at any moment. The smart users prefer to take the backup of desired database, which can be restored during unexpected conditions. Many cases have been reported where the users are completely hopeless as their backup files (.bak) got damaged or corrupted due to reasons like virus attack or malware etc. No provision is provided by the server or operating system to recover data from such backup files. However, various third party tools promise to repair damaged SQL backup files without any data loss. One such well-known automated tool is SQL Backup Recovery Tool, which empowers to repair full database from corrupted/damaged backup file. Our data recovery experts have undergone through the software and tested it under the following environment:
  • Operating System- Windows 8
  • Mounted RAM- 2 GB
  • Processor Used-2.5 GHz
The testing has been done in such a way that all of its features can be evaluated to determine the SQL backup recovery tool on the basis of quality, reliability, performance, security etc. The following section is focused to provide a transparent review of the tool on the basis of expert’s experience.

Functional Features of SQL Backup Recovery Tool

While working with the recovery tool in different working environments, the experts encountered several amazing features which differentiates it from other tools available in industry. Some of the major features, which makes the tool excel in recovery field are mentioned below:

Complete Backup File Recovery

The tool facilitates the recovery of all MDF and NDF files from the corrupted backup file i.e., various database components such as tables, views, functions, triggers, keys, indexes get recovered from the chosen backup file.

Dual File Scan Mode

The tool has been designed to offer scanning of damaged backup files in the following two modes:
  • Quick Scan - This mode scans the backup file with minimum corruption
  • Advance Scan - This mode scans the backup file with major corruption issues (highly damaged files)
The user can perform scanning in any of the mode, depending on file corruption extent.

Multiple Backup File Recovery

SQL backup recovery tool provides Multiple Backup File Options to let the user add multiple backup files or folder for data recovery. So if user faces an issue where multiple backup files got corrupted, this multiple backup option can be chosen to recover all data.

Preview Repaired SQL Data

Once the complete backup file is scanned, the tool provides preview of all repaired database components. The user can choose to preview all database component like tables, views, triggers, columns, functions, keys etc.

Batch export Recovered Data

The tool allows the user to export selected database components from the scanned data. The user can make selective choice to transport the required database objects from the backup file.

Multiple Export Options

The users are offered to export the repaired data in any of the following way:
  • Export To SQL Server Database - It allows to export selected repaired data directly into SQL Server by providing database credentials.
  • Export as SQL Server Compatible Script - It generates SQL script, which is compatible for any SQL Server version.

Export Schema Options

SQL backup recovery tool provides two export modes for the users. Depending on requirement, the user can choose to export data as:
  • With only schema - It allows to export only with database schema for the backup file
  • With schema & data - It allows to extract both data and schema for the backup file

Primary/Foreign Key Recovery

Along with the data recovery, the tool also helps to recover both primary and foreign keys of all the tables. So, we can say that the tool ensures to maintain data integrity of the data recovered from the backup file.

Perks of The Tool

In addition to above discussed features, the tool offers some additional features like auto-detection of SQL Server, no database size restriction, interactive user interface etc.

Specifications of SQL Backup Recovery Tool

The tool has been designed to work efficiently under the following hardware and software specifications:
  • Operating System - It supports Windows 8.1 & all below versions
  • Processor Requirement - Minimum 1 GHz is mandatory for tool installation. The experts suggest to use 2.4 GHz processor for faster processing.
  • Mounted RAM - 512MB should be allocated at minimum. For larger storage, 1 GB can be used depending on requirement.
  • SQL Server - It repair damaged SQL backup file of SQL Server 2014 & all the below versions
Available Versions of SQL Backup Recovery Tool
The tool to repair damaged SQL backup file can be availed in two versions:
  • Demo Version - Users can download the free version from company’s official website. It offers scan and preview of the backup files.
  • Licensed Version - The paid version of the tool can be purchased from company’s website. It allows to scan, preview and export the selected backup file
  • The tool provides recovery of backup files even without SQL Server installation on the system.
  • The tool offers the recovery of highly corrupted backup files using advance scan.
  • The generated scanned files cannot be saved on the local system, which creates need for rescanning in future use.

After working on various aspects of the software, we can conclude that SQL Backup Recovery tool fulfils all the expectations to repair damaged SQL backup file. The users can go for this tool for an efficient bak file recovery within few easy steps. The tool can be rated as 9.8 on the scale of 10 as it provides an excellent approach to repair the corrupted or damaged backup files using its interactive user interface.

About Daniel Jones
Daniel Jones is a SQL Server DBA and contributor at SQL Tech Tips. Having 2 + years of experience in SQL recovery and system infrastructure.

Social Media Profiles