Caveman's Blog

My commitment to learning.

Posts Tagged ‘

SQL Server: Restore a database from a .mdf file.

leave a comment »

In this blog post I will show you how to restore a database from a .mdf file alone. I am working with the AdventureWorks database in this demonstration. Download the .mdf file for the AdventureWorks database from CodePlex.

1. Open SQL Server Management Studio (SSMS).
2. Right click the Databases folder. select Attach from the context menu.
3. Click Add and select the appropriate .mdf file. Click Ok, and then click Ok again. You will get an error at this time because SSMS could not find the corresponding .ldf file.
4. Select the .ldf file entry and click Remove and click Ok.
5. You have successfully restored a database from the .mdf file.

Check out the steps as a pictorial in the slide show below:

This slideshow requires JavaScript.

SQL Server: How to select data from a stored procedure?

with one comment

In his post I will show you two ways of using SQL to query the data that is generated by the execution of a stored procedure.

Method 1: Using OPENQUERY [1] to be able to select the intended data. OPENQUERY executes the specified pass-through query on the specified linked server. “getEmployees” stored procedure returns all the records from the employee table. OPENQUERY executes the “getEmployees” stored procedure in the Demo database and also allows us to  execute a select command on the result set.

SELECT * FROM OPENQUERY ([SERVERNAME],'exec demo.dbo.getEmployees')

First time I ran the above query I got an error: Server ‘[SERVERNAME]’ is not configured for DATA ACCESS. We have to set the DATA ACCESS option of the SQL Server Instance to true, to be able to use OPENQUERY and that can be done as follows:

exec sp_serveroption @server = '[SERVERNAME]', @optname = 'DATA ACCESS', @optvalue = 'TRUE'

How you can find the SQL Server instance name?

The select statement with OPENQUERY executed successfully. At this point we should be able to select all or specific columns from the resultset of the stored procedure like in the example below:

Method 2: Using table variable to select the intended data. In this method we first create a table variable with a schema that matches the output of stored procedure execution. We then capture the output of the stored procedure execution in a table variable. At this point we will be able to scout through all the output data using DML.

declare @tempTable table(EmpID int, EmpName varchar(50), Salary int)

insert into @tempTable
exec demo.dbo.getEmployees

select * from @tempTable


SQL Server: Size of a Table and Database

leave a comment »

We can use sp_spaceused, a system stored procedure, to know the size of a table or a database. Here is a sample usage on a table. sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

sp_spaceused 'Employee'

We can use sp_msForEachTable in combination with sp_spaceused to iterate over all the tables and list the corresponding size of each table of a given database. The sample query lists the attributes of the 2 tables that exist in my sample database.

EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

The following example summarizes space used in the current database and uses the optional parameter @updateusage to ensure current values are returned for the Demo database.

EXEC sp_spaceused @updateusage = N'TRUE';

1. MSDN Online

SQL Server: How to check for the existence of a temp table?

leave a comment »

Temporary tables in SQL are created and maintained in the tempdb database. Here is the TSQL to create a temporary table called “#temp_employee” and the following that is code that lists all the tables in the tempdb database.

create table #temp_employee(emp_id int, emp_name varchar(50))

select * from tempdb.sys.tables

In the list of tables below the fifth row identifies the temp table that we have created above. The actual name of the temp table has got appended. In order to search for the existence of this table we have to do a wild card search on the tempdb like in the SQL below:

if exists(select * from tempdb.sys.tables where name like '#temp_employee%')
print 'temp tables exists'

SQL Server: How to move user database files?

leave a comment »

Today we had a situation where one of the drives of a box that hosts the database files of a SQL Server 2008 instance hardly had any more room to grow. Our options we had to handle this issue were a) adding more disk space b) move the database files to another drive that has a decent amount of free space. In this post I will show you how option b) can be implemented in a couple ways. Following are the some of the methods that can be employed for this purpose:

  1. Transact-SQL
  2. SQL Server Management Studio.
  3. Back and Restore

There could be two scenarios for this situation.

  • Moving the database files to a new drive, location with the same instance of SQL Server. We can use only options 1 or 2 for this scenario.
  • Moving the database files to a new SQL Server instance or server. We can use any one of the above mentioned methods for this scenario.

Moving the files using Transact-SQL.

Take a backup of your database before performing this activity. The first step in this process is to detach the database by using the sp_detach_db stored procedure. Execute this stored procedure against the master database like in the code below:

USE master

sp_detach_db 'Demo'

Now that the database has bee detached from the SQL Server instance, we can physically move the related .mdf and.ldf files to a new location. This step is followed by reattaching the files to the Sql server Instance like in the code below

USE master

sp_attach_db 'Demo', 'D:\NEWFILELOCATION\Demo.mdf', 'D:\NEWFILELOCATION\Demo.ldf'

Moving files using SQL Server Management Studio 2008

1. Right click on the database,  go to Tasks and click on Detach in the context menu.

2. Copy the file to a new location.

3. Right click on the instance of SQL Server. Click on Attach to see the “Attach Databases dialog box”.

4. Click the Add button related to the “Databases to attach” section and select the database files from the new location and click OK.

5. At this point you have successfully moved your database files to a new location.

1. MSDN Online

SQL Server: How to copy a very large table?

with one comment

This blog is an extension to my earlier post on how to copy a table. This post is specific to the situation where you have to copy huge  amount of data from your source table and to a destination table as quickly as possible. The amount of time to copy and the explosion of the log file are the two points of concern during this activity.

Let us look at a table with large amounts of data. We can use sp_spaceused on a table to find out the physical attributes. The following screen-cap tells us that the faded out table has close to 7 millions rows of data with the total storage size of 37 GB approximately.

It took more thank 10 hours to copy the data in the source table to a destination table in another database of the same SQL Server instance via the using standard Insert-Select Transact-SQL statements. The byproduct is this operation was large log file. The solution to deal with this issue would be the one where the copy action is not logged at all or is logged at the minimum. This would reduce the I/O and will speed up the copy process.

Bulk_Logged Recovery mode (plus “Select Into”) and BCP Utility are the two tools that can be used to achive faster copying times with no or minimal amount of transaction logging.  Following is the usage of technique # 1 via using the SELECT Into clause in tandem with the Bulk_Logged recovery mode to copy the same table. The first step in this process is to programtically or manually set the database recovery mode to “Bulk-logged”

--Programatically setting the Recovery mode
USE master;

You can manually set the recovery mode by accessing the Database properties dialog box:

Step 2 is to actual copy the data via executing the “Select Into” Transact-SQL. The last step in this process is to reset the database recovery mode to “Full”.

select * into DemoX.dbo.Employee from Demo.dbo.Employee

USE master;

Copy Time: 2.75 hours. This is pretty decent considering that the traditional copying took more than 10 hours to do the same job. My next goal is to use the BCP utility to copy the same table and compare the timings. Here is the basic idea of using this utility

Step 1: Export data to a file using the BCP utility.
Step 2: Import data into the destination table using the BCP utility.

Will post my results soon.

1. MSDN Online – Bulk Logged Recovery Model
2. MSDN Online – BCP Utility

SQL Server: How to search for text/phrase in a Stored Procedure?

with one comment

Following are some of the queries to find the stored procedures that contain a search text/phrase:



WHERE [text] LIKE '%employee%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1


FROM sys.sql_modules
WHERE definition LIKE '%employee%' and OBJECTPROPERTY(object_id, 'IsProcedure') = 1

Following some more “How to search” posts:

1. SQL Server: How to search for a number in a string?
2. SQL Server: how to search for a table name or a column name or a given stored procedure name?