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