Home » 2012 » November (Page 2)

Monthly Archives: November 2012

SQL Server || Compress & UnComprees Backups in one Media\File

Today, We try to check of SQL server when trying to take Compress & UnCcomprees Backups in one Media\File.

Query No. 1

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL.BAK’ WITH COMPRESSION

GO

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL.BAK’ WITH NOINIT

Result : Run Successfully

Explanation : Backup file is formatted to take compressed backup. So without mention COMPRESSION keyword. SQL server process this backup request as COMPRESSED backup.

Query No. 2

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL2.BAK’ WITH COMPRESSION

GO

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL2.BAK’ WITH INIT

Result : Run Successfully

Explanation : Backup set is overwritten but file header is intact. Due to which without mention COMPRESSION keyword, SQL server process this backup request as COMPRESSED backup.

Query No. 3

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL3.BAK’

GO

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL3.BAK’ WITH INIT,COMPRESSION

Result : Failed

Msg 3098, Level 16, State 2, Line 1

The backup cannot be performed because ‘COMPRESSION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘COMPRESSION’ or specify ‘NO_COMPRESSION’. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Explanation : Backup file is formatted to take Uncompressed backup but backup is mention to take in compressed form.

Query No. 4

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL4.BAK’

GO

BACKUP DATABASE DB2

TO DISK = ‘D:\DB2_FULL4.BAK’ WITH NOINIT,COMPRESSION

Result : Failed

Msg 3098, Level 16, State 2, Line 1

The backup cannot be performed because ‘COMPRESSION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘COMPRESSION’ or specify ‘NO_COMPRESSION’. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Explanation : Backup set is overwritten but file header is intact. Due to COMPRESSION keyword, SQL server tried to process this backup request as COMPRESSED backup in Uncompressed file format & got failed.

Happy Diwali………….

Wishing You a Very Happy Diwali…………….

KAHI TIMTIMAHAT HAIN DIYO KI, KAHI SHOR HAAN ATHISBAZI KA

CHAMAK HAAN DIL MAIN, UMEEDO KI KHANAK HAAN

MITHAIYO KA ANAND HAAN, PATHAKO KI JHADI HAAN

YE JEET HAAN SACH KI, YE JASAN HAAN DIWALI KA

Why SQL Server Named Instance connect without specifying instance name ?

Issue :

Today Evening, I was just about to leave the office and at same time I got a call from one of my friend. He is not running under any production issue but bit confused with SQL server behavior while connecting with port no. in case of named instance.

He is having one named SQL Server 2005 instance running on non-default port 55537.

He tried to connect by following Server Names :-

· MachineName\InstanceName – Working fine & he is having no issue

· MachineName\InstanceName,PortNo – Working fine & he is having no issue

· MachineName,PortNo – Now this is the issue.

He is running with question :-

1. How SQL server is going to connect without instance name?

2. Is it connecting some different instance? But his machine is having only one named instance.

Solution :

The correct syntax for connecting to SQL Server is “Servername/InstanceName” or “ServerName,port”. When you specify both an instance name and the port, the connection is made to the port number.

So when you mention Port with machine name, SQL server connect to the SQL instance running on mentioned port without being knowing instance name.

How to open and view a deadlock graph .xdl file with SSMS ?

How to open and view a deadlock .xdl file with SSMS ?

Solution 1 :

  1. On the File menu in SQL Server Management Studio, point to Open, and then click File.
  2. In the Open File dialog box, select your .xdl file & click open.

Solution 2 :

  1. Go to the directory, you have saved your deadlock graph .xdl file.
  2. Right click on .xdl file > Go to Open With Option > Select SQL Server Management Studio

SQL Server 2012 SP1 Is Now Available !

After a wait of around 8 months, Microsoft has released SQL Server 2012 SP1.

Microsoft SQL Server team has released SQL Server 2012 Service Pack 1 (SP1). Both, the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. SQL Server 2012 SP1 contains fixes to issues. It includes all released CUs before the release of SP1. Service packs are very critical and important. It is very important from product upgrade & bug fixing point of view.

Obtain SQL Server 2012 SP1

What’s New in SQL Server 2012 (Top features)

· Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade

· Selective XML Index

· DBCC SHOW_STATISTICS works with SELECT permission

· New function returns statistics properties

· SSMS Complete in Express

· SlipStream Full Installation

%d bloggers like this: