Category: General topics

Welcomes you to the first SQL Server 2016 event in the Middle East at AlFaisal University on 23/4/2016

We are so glad to announce SQL Gulf #3 event , Riyadh 2016 which will be held at Al-Faisal University on 23th April where top notch MVP, MCM and MCA experts are coming from different countries worldwide Australia , US, UK and Germany to speak to you about SQL Server 2016 ONLY…! It is the first event ever in the middle east to reveal about the latest Microsoft SQL Server 2016 technologies and techniques ,the event is for both male and female ,come to register here ASAP http://waja.com.sa/SQLGulf3/

 

Speakers 

Continue reading “SQL GULF #3 RIYADH 2016” →

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name. This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO

CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
GO

sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DP.sid
      , SP.name
      , DP.name
      , DP.type_desc
  FROM sys.database_principals DP
  LEFT JOIN sys.server_principals SP
    ON DP.sid = SP.sid
 WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
   AND DP.sid IS NOT NULL'
GO

  SELECT *
    FROM #Output
   WHERE ServerLoginName IS NULL
      OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName
GO

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

MS SQL Server is a relational database management system that is used for storing and retrieval of data when requested by other applications. The SQL database mainly comprises of three files- MDF, NDF and LDF. The first two files from the primary and secondary database of SQL Server and the latter stores the log database of SQL server database transactions. While the data files and the log file are accessed by a common server, these files do not share the same location for their storage. Both the data files and the log file are associated with SQL server; in this blog however, we will restrict our discussion to how to repair corrupted .mdf and .ldf files.

Primary & Log Database of SQL Server

Primary database aka Main Database File (MDF) as the name suggests is the main database file of SQL Server that are associated with other files. There exists only a single MDF file for every SQL Server database, which stores data items like triggers, views, stored procedures, etc. It would not be wrong to say that MDF file is the primary element for administration of SQL database.

The LDF (Log Database File) store the entire log information of the transactions or activities carried out in the SQL Server. They play a very crucial role in the restoration of SQL server data in cases of data disaster. The size limitation of LDF is decided at the time of the database designing. Information related to records updation, record deletion, insertion of new record, etc.

Corruption in MDF and LDF Files

Maintaining the database integrity of SQL server is the top priority of the users as any mishap can lead to loss of data that at times cannot be recovered easily. There can be umpteen reasons, which can end the MDF and LDF files in a corrupted state. Some of the reasons for MDF and LDF corruption are:

1. The biggest reason behind the corruption of MDF and LDF files is the storage size. In case the data exceeds the limit of MDF and LDF file, corruption is likely to occur.
2. In case the SQL server database is modified during the DROP operation, there are great chances of MDF and LDF files corruption.
3. Hardware issues such as problem in the hard drive sector storing the SQL database, may lead to corruption of the database.
4. Virus intrusion is also an important factor when it comes to corruption of MDF and LDF files.

Manual Solutions to Repair Corrupted MDF and LDF Files

In order to repair a corrupt MDF and LDF file, DBCC CHECKDB command can be used. It recovers the data from a corrupt SQL database file that has been damaged due reason such as logical corruption. This involves running an executable code in the SQL server in order to repair corrupted MDF and LDF files.

Note: Before running the DBCC CHCKDB command, the backup of the existing database should be taken.

In case the repair process fails and the backup of the MDF and LDF files does not exist, the user will not be able to restore the database back to its original state and hence will end up in having a corrupt MDF and LDF file.

Alternate Solution to Repair Corrupted MDF and LDF Files

Regarding the drawbacks of the manual procedure to repair corrupted MDF and LDF files, third party solutions are preferred over them. One such commercial utility that can be deployed for repair corrupted MDF and LDF files is SQL Server Database Recovery Tool. The software recovers the entire data from corrupt MDF and LDF files and save it in SQL compatible scripts or directly in SQL server database.