Search

Friday, December 2, 2016

The media family on device '' is incorrectly formed. SQL Server cannot process this media family. RESTORE FILELIST is terminating abnormally.

Error:

The media family on device ‘<FILENAME.BAK>’ is incorrectly formed. SQL Server cannot process this media family. RESTORE FILELIST is terminating abnormally.

Solution:

Ensure that backup file is good and copied properly.
You can not restore higher version databse Backup in lower version SQL Server.

Friday, October 21, 2016

Error occurred during installation: Error 1618 installing Microsoft SQL Server setup Support files.

You may get below error during SQL server installation:

Error occurred during installation: Error 1618 installing Microsoft SQL Server setup Support files.

Follow below steps to solve the error:

  1. Restart The system and try installing.
  2. Start - Run - Type: gpedit.msc - OK - Navigate to

Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment\Debug programs
Add administrator user.

Monday, May 30, 2016

Login failed for user ‘domain\username’. Reason: Server is in single user mode. Only one administrator can connect at this time.

You may get below error some time after improper shutdown of system.

Login failed for user ‘domain\username’. Reason: Server is in single user mode. Only one administrator can connect at this time.

Restart SQL server and Browser and problem will be solved.

Thursday, February 18, 2016

Query to get IP address of SQL Server

Use below query to get IP address of SQL Server:

DECLARE @IP varchar(40)
DECLARE @IPLine varchar(200)
DECLARE @Pos int

SET NoCount ON
SET @IP = NULL

Create Table #Temp (IPLine VarChar(200))
INSERT #temp EXEC master..xp_cmdshell 'IPconfig'

SELECT TOP 1 @IPLine = IPLine FROM #Temp
WHERE Upper (IPLine) LIKE '%IP ADDRESS%' OR Upper (IPLine) LIKE '%IPV4 ADDRESS%' 
    IF (ISNULL (@IPLine,'***') != '***')
      BEGIN 
       SET @Pos = CharIndex (':',@IPLine,1);
       SET @IP = LTrim(RTrim(SubString (@IPLine , @Pos + 1 ,len (@IPLine) - @Pos)))
      END 
PRINT @IP
DROP TABLE #temp
SET NoCount OFF

If you get below error after executing above query:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Than you had to enable xp_cmdshell in surface area configuration. and than rerun the above query.

Monday, February 8, 2016

How to show backup or restore progress to user in a progressbar?

You can show backup or restore progress to user in a progress bar if you had installed 

sp_who2k5 

into your master database. You have to run the below command:

sp_who2k5 1, 1

The result will include all active transaction. Search the backup in the requestCommand field. The aptly named percentComplete field will give you the progress of the backup. 

Monday, February 1, 2016

The SQL Server system configuration checker cannot be executed due to WMI configuration on the machine.

Last week I got below error in clients system:

The SQL Server system configuration checker cannot be executed due to WMI configuration on the machine. Error 2147749896

The main cause of the problem are:

Unfinished SQL Server components may be present.
Components or files related to WMI service might be unregistered or missing.
Microsoft Data Access Components may not be installed.
The problem was that the WMI repository is corrupted. Reinstall the WMI.

Solution:
Check the version of MDAC you have installed. SQL Server will require MDAC 2.8 or higher. To check MDAC:
Click Start / Run. Type regedit. Click OK.
Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess
Note the Registry Key for Version. If less than 2.8.xxxx,
download and install the latest MDAC 2.8 version.

Monday, January 11, 2016

Error: 64, connection was successfully established with the server, but then an error occurred during the pre-login handshake.

I had got below error in my friend's system:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

I googled this error and got mixed results and solutions but none solved my problem.


After some more googling I found below solution that worked for me.
The solution is:
Go to the MSSQL Configuration Manager and expand the SQL Network Configuration and click on the PROTOCOLS node. Right click on TCP/IP and open up the PROPERTIES panel and Select the IP ADDRESS tab.
Check the IP ADDRESS field's value are correct and match the system it is running on.
Restart the service, and the problem is solved.
Make sure you fill in the TCP PORT, even if you are using the default 1433.

Monday, January 4, 2016

Error occurred during database creation

Sometime when you create a new database by using CREATE Database statement in SSMS (SQL Server Management Studio), you may got below error message and you are not able to create Database:

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name '' may be incorrect.

Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors. 

The main reason of above error is path of folder store in the registry values does not exist. When the path of folder is incorrect then MS SQL server throws above error messages.

You can fix the error by using SSMS. SSMS is able to set the default directory value for Data and log Files. Follow below steps to change the default value: 


  • Start SQL server Management Studio.
  • In Management Studio, right click on your instance and select properties
  • In properties, click on the database setting option.
  • Now go to the new database default location section
  • Correct folder path from default directory box and default log directory box
  • Press OK
  • Stop the instance of MS SQL database
  • Restart the instance of MS SQL server database.