How to attach a SQL Server database when ldf (Log) file is missing?

In this blog post we shall learn how to attach a database when the Database Log file is missing. Attaching a database which has all both the data and log files intact, is quite straight forward, you show where the data and log files are residing on your machine and the database will be attached. But when the log file is missing there is a little change to that procedure..

For this example, I downloaded the sample database for SQL Server 2012 from the Microsoft official site for SQL Server sample databases. First lets us place the Primary Data file (.mdf) file in the Data directory of the SQL instance, so I copy the data file (pic below) and paste it in the Data Directory (it might be different on your Server).

The picture below is my Data directory. The reason for placing the mdf file in the data directory is simple, when you try to attach a database, the wizard by default opens the data directory of the SQL Instance. You might also have the mdf file elsewhere, but in the wizard you will have to browse to that location..

Open the SQL Server Management Studio and connect to the SQL Instance, in the Object Explorer, right click the Databases node and select Attach from the popup menu.

Attach databases wizard is displayed as shown below. Click on Add button..

Then the below screen appears where we have to choose the mdf file of the database that we are planning to attach. As mentioned earlier, this locate database files dialog by defaults shows the Data directory of the Instance. If your mdf file is in a different location, you need to expand the folder tree to locate the file. Once the file is located, select the file and click OK button.

The wizard will then display the details of the database being attached, and note that the wizard is expecting a Log file (.ldf) in the log directory of the SQL Instance. Since it did not find any file in that directory the Message is displayed as Not Found. In the bottom portion of the window, select the row that displays the info about the log file. Click Remove button in the bottom right of the window.

After you removed the row, the window should look like the one shown in the pic below. Now click OK to attach and create the database.

Once the database is attached, right click the database and choose Properties and you click the Files page on the left side. You should see the database file of the database that was attached. See that the Log file is created with default database settings.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

What are SQL Server DBA Responsibilities?

This is one of the most frequently asked questions by people who are new to SQL Server. Since they are new to SQL Server, their concern is understandable. I was personally asked this question or this list multiple times and most recently, as comments to one of my blogs.

So I decided to list down a list of tasks and responsibilities carried out by a SQL Server DBA. Before further reading, let me tell you that roles and responsibilities of a SQL Server DBA varies from one organization to another as no two organizations IT setup are exactly similar.  For better understanding, the text in the parenthesis (Italicized blue font) is the subject area or chapter that has the topics to carryout the responsibilities…

  • Installation, Administration and Maintenance of SQL Server Instances. (Installing SQL Server)
  • Setup Test, Dev, Staging and Production Environments. (Installing SQL Server)
  • Create Users and assign permissions based on the level of database access the user would need. (Security)
  • Create Linked Servers to SQL Servers and other databases such as Oracle, Access, Informix etc. (Security and General Administration)
  • Design database Backup and Restoration Strategy. (Database Backups and SQL Server Agent)
  • Once created the database Backups, monitor those backups are being performed regularly. (SQL Server Agent)
  • From time to time recover the databases to a specific point of time, as per the requests. (Database Backups and Recovery)
  • Setup High-Availability as part Disaster Recovery Strategy for the Databases. (Failover Clustering, Database Mirroring, Log Shipping and Replication)
  • Troubleshoot various problems that arise in a day-to-day work and fix the issues. (Monitoring SQL Server Error Logs and checking your email alert (if there is one configured))
  • Monitoring and Performance Tuning; Physical Server Level, Database level (Database settings and options) and query tuning. (Creating and maintaining those Indexes, not performing database shrinking, memory settings, monitoring CPU usage and Disk I/O activity etc) 
  • Documenting major changes to the SQL Servers. (General)
  • Apply Service Packs. (General)

Note: These are only some of the roles carried out by a SQL Server DBA. If you have more questions, please let me know through comments.. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

How to uninstall SQL Server 2012?

This blog is actually in response to comments to one of my previous blogs Installing SQL Server 2012 Release Candidate 0 posted earlier this week. The user asked the steps to uninstall SQL Server 2012 RC 0, so I thought to write a short blog to help others also, who are looking for steps to uninstall SQL Server 2012, for that matter the steps to uninstall other versions like SQL Server 2008 R2 and 2008 are also very similar to the steps in this blog..

So let us look at the steps to uninstall SQL Server 2012. 

On the machine where SQL Server 2012 is installed (in my case it is Windows Server 2008 R2) clicks Start and select Control Panel.

When control panel is displayed, under Programs section, click Uninstall a program.

Then the list of programs and features installed on that machine is displayed as below.

Click on Microsoft SQL Server 2012 RC0 (as mentioned earlier, if you are trying to uninstall SQL Server 2008 or 2008 R2, choose that Version).  A small window similar to the SQL Server Installation splash screen is displayed.

Click on Remove and you should see Setup Support Rules windows displayed which is displayed below.

Click OK to proceed. In the next screen Select Instance screen, choose what you want to uninstall from that machine. There will be a drop down displayed from which we need to select the Instance which we want to uninstall now. If there are multiple Instances on that machine, you would need to be careful in choosing which Instance to uninstall. Also, the list of Instances and their Versions (if there are multiple instances) will be displayed in the a grid below for an overall summary of what are on that machine. Once you choose your SQL Server Instance click Next. In the next window (pic below) select the features that you want to install from the machine. Since I wanted to show the steps to uninstall the entire SQL Server 2012 setup, I selected all features. If your machine has other Versions of SQL Server like SQL 2008 or 2008 R2, some of these components should not be uninstalled.

Click Next to proceed.

Then, there are some removal checks that are done to make sure the uninstall will run smoothly. If there are no failures, click Next.

Click Remove to start the uninstall process of SQL Server 2012. You should be seeing the progress window of the SQL Server uninstall. Once the process is completed, there should be a final screen saying that SQL Server is uninstalled. I did not uninstall the SQL Server as I would need it to work on, so I have not shown those 2 screenshots..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty