SQL SERVER – Find Current Location of Data and Log File of All the Database

As I am doing lots of experiments on my SQL Server test box, I sometime gets too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored. I often go to that folder and clean up all unnecessary files I have left there taking up my hard drive space. I run following query to find out which .mdf and .ldf files are used and delete all other files. If your SQL Server is up and running OS will not let you delete .mdf and .ldf files any way giving you error that file already in use. This list also helps sometime to do documentation of which files are in being used by which database.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

Following is the output of files used by my SQL Server instance.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Reasons to Backup Master Database – Why Should Master Database Backedup

The most interesting thing about writing blog at SQLAuthority.com is follow up question. Just a day before I wrote article about SQL SERVER – Restore Master Database – An Easy Solution, right following it, I received email from user requesting reason for importance of backing up master database.

Master database contains all the system level information of server. Information about all the login account, system configurations and information required to access all the other database are stored in master database. If master database is damaged, it will be difficult to use any other database in SQL Server and that makes it most important database of the SQL Server.

Let us understand the important of the master database using an example. We will take example of SQL Server DBA and follow his timeline. Make sure to understand it correctly, as I have small question at the end of the timeline.

9:00 AM – DBA takes backup of the master database.
10:00 AM – DBA creates new Database named AfterMaster.
11:00 AM – DBA restores the master database backup taken at 9:00 AM.
12:00 PM – I have following two questions for the DBA :

Question 1) What will be the state of the database AfterMaster? If AfterMaster database will be in active state after restoring master database?
Question 2) What should be the next step after restoring master database?

Let us understand the answer of question.

Answer 1) Once master database is restored it will have no record of AfterMaster database in its system database and it will not recognize it.
Answer 2) If master database is restored from backup all the operation which are done after last master database backup should be repeated in order to bring SQL Server in the current operational state. In our case, the database files (ldf and mdf) of AfterMaster database will still exists on server. They should be reattached to the server. You can search about how to attach mdf and ldf file at Search@SQLAuthority.com.

It is clear from our example that master database contains user login, files, filegroups and server wide settings.

In summary, it is extremely important to take backup of the master database.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Restore Master Database – An Easy Solution

Today we will go over two step easy method to restore ‘master’ database. It is really unusal to have need of restoring the master database. In very rare situation this need should arises. It is important to have full backup of master database, without full backup file of master database it can not be restored.

It is necessary to start SQL Server in single user mode before master database can be restored. It is very easy to start SQL Server server in single user mode. Follow the tutorial SQL SERVER – Start SQL Server Instance in Single User Mode.

Once SQL Server instance is running in single user mode, immediately connect it using sqlcmd and run following command to restore the master database.

RESTORE DATABASE master FROM DISK = 'C:\BackupLocation\master.back' WITH REPLACE;

I have tested it couple of times and it has worked fine for me. If you encounter any error please leave a comment and I will do my best to solve it.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Start SQL Server Instance in Single User Mode

There are certain situation when user wants to start SQL Server Engine in “single user” mode from the start up.

To start SQL Server in single user mode is very simple procedure as displayed below.

Go to SQL Server Configuration Manager and click on  SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Make sure to remove newly added params after required work is completed to restart it in multi user mode.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation

Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you.

Hello Pinal,

I have seen many blogs from you on SQL server and i have always found them useful and easy to understand. Thanks for all the information you provide.

I have one query about shrinking NDF and MDF files.

Can we shrink NDF and MDF files?? If you do so is there any data loss?

I have been shrinking the .LDF files every now and then but I am not too sure about NDF and MDF files.

Can you please answer my query.

Waiting for your early response.


Shrinking MDF and NDF file is possible and there is no chance of data loss.

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.

Following is the script to shrink single file.
DBCC SHRINKFILE (logicalLogFileName)

To find logicalLogFileName following command has to be ran.
USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – CWE/SANS TOP 25 Most Dangerous Programming Errors

I just came across very interesting article from SANS Institute. Experts from more than 30 US and international cyber security organizations have released list of 25 most dangerous programming errors and their resolution. It may be possible that many of the programmers may not understand what this errors are and how to implement their solution. As said this are 25 most dangerous errors and all the developers should atleast know what they are so they do not are prevented from origin. Here are four major advantages listed by SANS.

  • Software buyers will be able to buy much safer software.
  • Programmers will have tools that consistently measure the security of the software they are writing.
  • Colleges will be able to teach secure coding more confidently.
  • Employers will be able to ensure they have programmers who can write more secure code.

Following is the list of 25 most dangerous programming errors.

CATEGORY: Insecure Interaction Between Components

CWE-20: Improper Input Validation
CWE-116: Improper Encoding or Escaping of Output
CWE-89: Failure to Preserve SQL Query Structure (aka ‘SQL Injection’)
CWE-79: Failure to Preserve Web Page Structure (aka ‘Cross-site Scripting’)
CWE-78: Failure to Preserve OS Command Structure (aka ‘OS Command Injection’)
CWE-319: Cleartext Transmission of Sensitive Information
CWE-352: Cross-Site Request Forgery (CSRF)
CWE-362: Race Condition
CWE-209: Error Message Information Leak

CATEGORY: Risky Resource Management

CWE-119: Failure to Constrain Operations within the Bounds of a Memory Buffer
CWE-642: External Control of Critical State Data
CWE-73: External Control of File Name or Path
CWE-426: Untrusted Search Path
CWE-94: Failure to Control Generation of Code (aka ‘Code Injection’)
CWE-494: Download of Code Without Integrity Check
CWE-404: Improper Resource Shutdown or Release
CWE-665: Improper Initialization
CWE-682: Incorrect Calculation

CATEGORY: Porous Defenses

CWE-285: Improper Access Control (Authorization)
CWE-327: Use of a Broken or Risky Cryptographic Algorithm
CWE-259: Hard-Coded Password
CWE-732: Insecure Permission Assignment for Critical Resource
CWE-330: Use of Insufficiently Random Values
CWE-250: Execution with Unnecessary Privileges
CWE-602: Client-Side Enforcement of Server-Side Security

Please go to SANS site and click on each error to find their resolution.

Reference : Pinal Dave (http://blog.SQLAuthority.com), list of 25 most dangerous programming errors