Home » 2012 (Page 2)

Yearly Archives: 2012

SSMS 2012 || Connect to Server (Additional Connection Parameters Page)

The Connect to SQL Server Management Studio presents a new Additional Connection Parameters Page. Use the Additional Connection Parameters page to add more connection parameters to the connection string.

Steps to get Additional Connection Parameters page

  1. In Management Studio, on the Query menu, point to Connection, and then click Connect.
  2. In the Connect to dialog box, click Options, and then click the Additional Connection Parameters tab.

What is the use of Additional Connection Parameters page ?

· Additional connection parameters can be any ODBC connection parameter.

· Additional connection parameters should be added in the format ;parameter1=value1;parameter2=value2.

· Parameters added using the Additional Connection Parameters page are added to the parameters selected using the Connect to dialog box options.

· The last instance of each parameter provided overrides any previous instances of the parameter. Parameters added using the Additional Connection Parameters page follow and replace the parameters provided in the Login or Connection Properties tabs. For example, if the Login tab provides SERVER1 as the Server name, and the Additional Connection Parameters page contains ;SERVER=SERVER2, the connection will be made to SERVER2.

· Parameters added using the Additional Connection Parameters page are always passed as plain text.

· Do not include login credentials and passwords in the Additional Connection Parameters page. They will not be encrypted when they are passed across the network. Use the Login tab instead.

· You can use it for both Database engine Services or Analysis Services.

Example :-

1) We have not mention user password in login page

2) We have enter, Server Name, database name, User Name & password like parameter in Additional Connection Parameters Page & we connect successfully on MSDB database with test user.

Helpful Link from MSDN

Reference : Rohit Garg (http://mssqlfun.com/)

Become Microsoft Community Contributor …..

Become Microsoft Community Contributor.

I am very happy after receiving this affiliation from Microsoft last month.

My MCC badge is visible now on MSDN.

Profile Page : http://social.msdn.microsoft.com/profile/rohitgarg/

MCC

 

 

 

 

Reference : Rohit Garg (http://mssqlfun.com/)

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.

%d bloggers like this: