SQL Server Backup compression

How to do Backup compression in SQL Server?

The backup compression  option determines whether SQL Server creates compressed or uncompressed backups .Backup compression option is off by default in SQL Server.The default behavior  can be modified by sp_configure option “backup compression default” .

Syntax:

USE master;
GO
EXEC sp_configure ‘backup compression default’, ‘1’;
RECONFIGURE WITH OVERRIDE;

To override the backup compression:

You can change the backup compression behavior for an individual backup by using WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement. We cannot take compressed and non-compressed backups on the same file. If we take COMPRESSION  backup on a file were  already non-compressed backup has taken,error will be shown.So we have to use different files for compressed and uncompressed backup.

Example to take backup for AdventureWorks with NO_COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uncompressed.bak’ WITH NO_COMPRESSION
GO

Example to take backup for AdventureWorks WITH COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\compressed.bak’ WITH COMPRESSION
GO

To calculate the compression ratio of a backup:

After taking backup with compression and without compression the backup_size can be compared to see the difference.

Syntax:

select backup_size,compressed_backup_size,100- ((compressed_backup_size/backup_size)*100) as “compressed %”   from msdb..backupset

 image

Types of isolation levels in SQL Server

The ISO standard defines the following isolation levels in SQL Server Database Engine:  

Microsoft SQL Server supports these transaction isolation levels:

Read Committed

SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC.

Read Uncommitted

SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server.

Repeatable Read 

SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. So phantom rows are This has the same effect as specifying HOLDLOCK on a SELECT statement.

(Phantom read:Phantom reads occurs when an insert or delete action is performed against a row that is being read by a transaction.The second  transaction read shows a row that did not exist in the original read as the result of an insertion by a different transaction or due to deletion operation some rows  doesn’t appear)

Serializable

     In serializable read phantom reads are not allowed because while the first transaction is in progress other transaction wont execute.

Snapshot

SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursor is populated with the values as of the time when the transaction first started. Scroll locks are still requested regardless of use of snapshot isolation.

 

Read uncommitted example: Uncommitted Read allows your transaction to read any data that is currently on a data page, whether that has been committed or not. For example,another user might have a transaction in progress that has updated data, and even though it’s holding exclusive locks on the data, your transaction can read it anyway.

image

If we execute the select query before the update transaction gets committed,it will not wait for the update transaction to commits.Query will be executed immediately without any time lapse.

image

Read Committed example

Read committed allows your transaction to read only if the data is committed.Read Committed operation never reads data that another application has changed but not yet committed.

image

If we execute the select query before the update transaction gets committed,it will wait till the update transaction gets committed.

image

Repeatable Read example :In Repeatable Read issuing the same query twice within a transaction will not make any changes to data values made by another user’s transaction.Repeatable Read allows phantom reads(Data getting changed in current transaction by other transactions is called Phantom Reads).So phantom rows will appear.

image

While the transaction(first query) is in progress,repeatable read allows another transaction(second query) to execute.It means it allow phantom reads.So second transaction(second query),need not wait till first transaction(first query) completes.Here values will be added before first query completes.

image

Serializable example : The Serializable isolation level adds to the properties of Repeatable Read by ensuring that if a query is reissued, rows will not have been added in the table. In other words, phantoms will not appear if the same query is issued twice within a transaction.

image

While the transaction(first query) is in progress,serializable read does not  allow another transaction(second query),It means it don’t allow phantom reads.So second transaction(second query), must wait till first transaction(first query) completes.Here values will be added only after first query completes.

image

snapshot example : To use the snapshot isolation level you need to enable it on the database by running the following command

ALTER DATABASE DBname
SET ALLOW_SNAPSHOT_ISOLATION ON

SQL Server database snapshot

What is the use of SQL Server database snapshot and how to create SQL Server database snapshot?

Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

A database snapshot is a read-only, static view of the source database.Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.A snapshot persists until it is explicitly dropped by the database owner.

Snapshots can be used for reporting purposes.In the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot’s creation.

Creating a series of snapshots over time captures sequential snapshots of the source database. Each snapshot exist until it is dropped.Each snapshot will continue to grow as  pages in original database are updated, you may want to conserve disk space by deleting an older snapshot after creating a new snapshot.

Steps to create database snapshot in SQL Server:

1.Create a new database or use existing database to create the database snapshots.

Syntax:

USE master
GO
— Create database
CREATE DATABASE test
GO
USE test
GO

2.Create table  and Insert values into table.

Syntax:

–create a Table
CREATE TABLE test1 (a INT, b INT,c INT)
—Insert values
declare @a nchar(10)
set @a=1
while (@a<5)
begin
insert into test1 values (@a,’1′,’1′)
set @a=@a+1
end

3.Create a snapshot  for a source database.

Syntax:

— Create Snapshot Database
CREATE DATABASE Snapshottest ON
(Name =’test’,
FileName=’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testsnap.ss’)
AS SNAPSHOT OF test;
GO

image

Snapshottest database is created successfully.

4.Now let us view both the source database and snapshot database.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

Data’s in tables of source and snapshot database are same.

 image

Size on disk  of the testsnap .ss could be viewed by opening its properties window.Let us note the  size on disk  of the source database.

image

5.Let us update existing rows in the table.

Syntax:

update test1 set a=0,b=0,c=0

6.After updating rows ,let us view the source and snapshot database.The values are updated only in source database,not in snapshot database.Because snapshot database is consistent with the source database as of the moment of the snapshot’s creation.

Syntax:

— Select from test and Snapshottest Database
SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;
GO

image 

But size on disk is increased,It clearly shows that when we update data in Source database, it copies the old/existing data pages to Snapshot database. This is the reason why the size of the snapshot database is increasing while updating the source database.

image

7.We could revert the source database.Reverting overwrites updates made to the source database since the snapshot was created by copying the pages from the sparse files back into the source database.

Syntax:

— Restore old data from Snapshot Database
USE master
GO
RESTORE DATABASE test
FROM DATABASE_SNAPSHOT = ‘Snapshottest’;

8.View the data in the tables from the source and the snapshot database after the restore from snapshot.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

image

9.We drop the snapshot database like any other database using drop database command.

Syntax:

— drop snapshottest
DROP DATABASE [snapshottest];

10. We can also create database snapshot on mirror database for load balancing.

How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server  can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream  column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table. 

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(Bulk  ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table         
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img

clip_image001[8]

4.To view the table with filestream included column

image

How to enable and configure Filestream in SQL SERVER 2008 / 2012

How to enable Filestream in SQL SERVER 2008 and SQL Server 2012?

Filestream was introduced in SQL Server 2008 for the storage and management of unstructured data.

Follow the below steps to enable this filestream in SQL Server2008.Let us see one by one.

To enable filestream through SQL Server configuration manager:

1.Open SQL Server configuration manager.Open SQL Server services

2.Select the instance for which you want to enable Filestream.Right click the instance->properties.

3.In the SQL Server Properties dialog box, click the Filestream tab.

4.Select the Enable Filestream for Transact-SQL access.

5.If you want to read and write Filestream data from Windows, click Enable Filestream for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

6.If remote clients must access the Filestream data that is stored on this share, select Allow remote clients to have streaming access to Filestream data.

7.Click Apply.

clip_image001[4]

Enable Filestream access level server configuration option:

In SQL Server Management Studio, click New Query.Execute the below query

[0 -Disables FILESTREAM,1 -Enables FILESTREAM for T-SQL,2 -Enables FILESTREAM for T-SQL and Win32 streaming access]

Syntax:

EXEC sp_configure filestream_access_level,2

RECONFIGURE with override

Create filestream enabled database:

  1. We can enable file stream while creating the database  (or) If the database is already created we can enable filestream using alter database.

To create file stream enable database you can use below query

CREATE DATABASE DBname
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

 

Enable filestream on existing database:

To enable file stream on existing database you can use alter database  command with  SET FILESTREAM similar to the example below or SSMS 

ALTER DATABASE [DBNAME] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’Directoryname’ ) WITH NO_WAIT
GO

 

Enable filestream for database using SQL Server 2008 Management Studio:

1. Connect to SQL Server Instance using SQL Server Management Studio

2. In the Object Explorer, right click the instance and select Properties.

3. On the left panel click on the Advanced tab, then click on the drop down list of Filestream Access Level and select Full access enabled option.

image

4. Click Ok to save the changes.

How to import/Export data in SQL Server

SQL Server Import and Export Wizard can be used to copy data from a source to a destination. source and destination can be sqlserver or any other connection.

Steps to export data in SQL Server 2008:

In Microsoft SQL Server Management Studio, expand the databases.

Right-Click on the database you want to copy to another database->Tasks->Export data/import data.

clip_image001

SQL Server Import and Export Wizard window will open.

clip_image001[4]

Click  Next to continue. Now, you will need to choose a Data Source. You can leave the Data source as SQL Server Native Client 11.0. Enter the Server name. Check windows Authentication  for your database. From the Database dropdown menu, select the name of your database.

clip_image001[22]

Click on Next to continue.

In the Choose a Destination window, you can select a different option to export to Microsoft Access, Microsoft Excel,SQL Server database,etc.,

clip_image001[26]

Then select the destination database,where you want to export your data.

clip_image001[24]

Specify whether to copy tables or to copy the results of a query from the data source.Click Next.

clip_image001[28]

Select source tables and click Next.

clip_image001[30]

Check Run immediately.Click Next.

clip_image001[32]

Click Finish to complete the wizard.

clip_image001[34]

Close the wizard after completing it successfully.

clip_image001[36]

Create script for all objects in database with data

You can create T-SQL scripts for database, objects in database and insert script for data in tables by using the Generate Scripts Wizard.

These scripts can be used to create database in secondary site with and with out data.

I this blog I will explain how to generate T-SQL script to create database, database objects and data.

Right click the database which you want to script—>Select tasks—>Generate scripts.

clip_image001

 Generate and Publish Scripts wizard screen appears.click Next to continue.

clip_image001[4]

In the Choose Objects wizard screen Select “Script entire database and all database objects” option if want to script all the objects in database or select specified objects which you would like to script and click Next to continue.

clip_image001[6]

In the Set Scripting Options wizard screen.Select the output type as “Save scripts to a specific location” and specify the path where you would like to save the database script file .Click the Advanced button  for specifying the schema and data scripting option.

clip_image001[8]

In Advanced Scripting Options screen; choose the option Schema and data for the Types of data to script option if you want to generate insert scripts for rows in tables  and click OK to save the changes and to return to Set Scripting Options wizard screen.

a) Data only – If this option is selected, it will only script out data within the tables
b) Schema and data – If this option is selected, it will script out Schema as well as the data within the selected objects
c) Schema only – If this option is selected, it will script out the Schema only

clip_image001[10]

In the Summary wizard screen; you will be able to see a quick summary of all the options which you have selected so far. Click Next to confirm and generate the scripts.

clip_image001[12]

In Save or Publish Scripts wizard screen; you will be able to see a Success or Failure message against each object for which the script was requested to be generated. Finally, click Finish to close the wizard.

clip_image001[14]

Once the script is generated you can view it like any other SQL Script. 

clip_image001[16]