Restore SQL Server database with TSQL

Recently I had troubles restoring a database in my SQL Server 2005 instance (SP3). I used the ‘wizard’ in the SQL Server Enterprise manager but after I got the message ‘restore completed successfully’ the database hung in ‘Restoring’ state, blocking connections. …

I decided the try to restore the database with T-SQL, with T-SQL the restore completed successfully! Another advantage of using T-SQL is that T-SQL gave more logging. This logging shows me that the BAK file (delivered from a third party) is not a SQL Server 2005 backup but presumably a SQL Server 2000 database backup … …

	@BackupFile varchar(8000),
	@sql varchar(8000)
	SET @BackupFile = 'E:\DB2000.BAK'
	SET @sql = 'RESTORE DATABASE ADB01 FROM DISK = ''' + @backupfile + ''' WITH FILE = 1,  
		MOVE N''DB2000_dat'' TO N''E:\mssql\data\ADB01\ADB01_01.mdf'',  
		MOVE N''DB2000_log'' TO N''F:\mssql\tlog\ADB01\ADB01_01.ldf'',  
	exec (@sql)

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 2792 pages for database 'ADB01', file 'DB2000_dat' on file 1.
Processed 1 pages for database 'ADB01', file 'DB2000_log' on file 1.
Converting database 'ADB01' from version 539 to the current version 611.
Database 'ADB01' running the upgrade step from version 539 to version 551.
Database 'ADB01' running the upgrade step from version 551 to version 552.
Database 'ADB01' running the upgrade step from version 552 to version 553.
Database 'ADB01' running the upgrade step from version 553 to version 554.
Database 'ADB01' running the upgrade step from version 554 to version 589.
Database 'ADB01' running the upgrade step
Read more →

osql switches

Some osql switches. …

Specify non default tcp ports
C:\>osql -Stcp:<hostname>,<portname>[\<instance_name>] -E

See …

Read more →

Remove SQL Server database from single-user mode

Braindump of a session where I had to remove a SQL Server database from single-user mode. …

execute sp_dboption
1> exec sp_dboption 'testdb01', 'single user', 'FALSE';
2> go
Msg 5064, Level 16, State 1, Server REMIDIAN01, Line 1
Changes to the state or options of database 'testdb01' cannot be made at this
time. The database is in single-user mode, and a user is currently connected to
Msg 5069, Level 16, State 1, Server REMIDIAN01, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

Now I can use TSQL below to find all the database sesions connected to this database. …

TSQL script to retrieve client sessions per database.
select, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where = 'testdb01'

Kill the particular session(s) with the ;kill’ command. …

1> kill 51
2> go

Now I can ‘remove’ the database from Sinlge user mode. …

1> exec sp_dboption 'testdb01', 'single user', 'FALSE'
2> go
Read more →

Microsoft SQL Server 2005 JDBC Driver (oracle GRID)

Microsoft SQL Server 2005 JDBC Driver java test program. …

Enterprise Manager Grid Control System Monitoring Plug-in for Microsoft SQL Server

I have been busy deploying Microsoft SQL Server in our Oracle Enterpise GRID Control using the ‘Oracle Enterprise Manager Grid Control
System Monitoring Plug-in for Microsoft SQL Server’ …

The final step in the configuration is the configuration of your SQL Server target in the GRID Control ‘configure target’ page for that SQL Server. One of the fields required is ‘JDBC URL’ (and ‘JDBC Driver’). …

The Microsoft SQL Server 2005 JDBC Driver comes with two sample programs in sqljdbc_1.2\enu\help\samples\connections ( and Since one of the prerequisities I read on metalink is ‘make sure remote JDBC connections with your SQL Server work’ I left the Oracle GRID console and tried to get this sample program to connect to my SQL Server succesfully. …

It took my quit some time to get the right connection values (my SQL Server listened on TCP port 1204 instead of 1433), and because I got a little tired of having to compile the provided java test pogram every time I wanted to try new connection details I decided to rewrite this program into a program that ‘asks’ for the connection values. This way I can provide the entries connection details at run time (instead of at compile time). …
import java.sql.*;

public class jdbcMSSql {
  public static void main(String[] args) {
    // prepare console 
    Console c = System.console();
Read more →

Create, drop and rename a database

Create, rename and drop a database in sql server … …

Create database

See syntax below for the CREATE DATABASE statement. …

Create NORTHWIND database
1> use master
2> go
1> create database [NORTHWIND]
2>     on ( name        = N'NORTHWIND_dat',
3>          filename    = N'C:\\mssql\\data\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.mdf' ,
4>          size        = 10,
5>          filegrowth  = 1,
6>          maxsize     = 1024
7>        )
8> log on ( name        = N'NORTHWIND_log',
9>          filename    = N'C:\\mssql\\tlog\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.ldf' ,
10>         size        = 10,
11>         filegrowth  = 1,
12>         maxsize     = 1024
13>        )
14>  collate Latin1_General_CI_AS
16> go
The CREATE DATABASE process is allocating 10.00 MB on disk 'NORTHWIND_dat'.
The CREATE DATABASE process is allocating 10.00 MB on disk 'NORTHWIND_log'.
Drop database

A database can be dropped using the DROP DATABASE statement. All database datafiles and logfiles are removed from the os. …

Dropping a database
Read more →

Control Structures

Control structures syntax; IF THEN ELSE, CASE, WHILE . …


The IF THEN ELSE syntax in TSQL is kinda weird if you ask me … I forget them ‘all the time’, created examples below for my reference. …

if 1 = 1
  print 'true'


IF THEN – short notation
if 1 = 1
  print 'true'


if 1 = 1
  print 'true'

  print 'false'


IF THEN ELSE – short notation
if 1 = 1
  print 'true'
  print 'false'

Multiple IF THEN ELSE blocks
if 1 = 1
  print '1 = 1'
if 1 = 2
    print '1 = 2'
if 1 = 3
  print '1 = 3'



TSQL has the CASE control structure … …

declare @i int, @r int

set @i = 1

set @r = 
  case @i
    when 1 then 1
    when 2 then 2
      else 0

print @r


WHILE Eternal loop
while 1 = 1 
  print 'true'


Simple WHILE loop
declare @i int
set @i = 0

while @i < 10
  print @i
  set @i = @i + 1


Read more →

Use filters to keep your traces meaningfull

Say you have an sql trace for the event ‘TSQL SQL:StmtCompleted’ you are, most probably, NOT interested in the sql statements executed through the SQL Server Agent for instance. Filters below remove most of the unwanted ‘internal’ tsql. …

The contents of your tracefile will become more meanigfull and not unimportant small … …

Filter out ‘internal’ sql server proceses Filter on ‘ApplicationName’
-- --
-- ApplicationName

-- SQL Profiler sessions
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- SQL executed by the SQL Agent
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLDMO_1%'

Filter on ‘LoginName’
-- --
-- LoginName

exec sp_trace_setfilter @TraceID, 11, 0, 7, N'remivisser'
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'<username>'
Read more →

cursors in tsql

Cursors in tsql, it can be done … …

Simple cursor loop

Example below is the most straight forward example of walking through a CURSOR with a WHILE loop in tsql. The cursor is declared, opened and and a FETCH NEXT is done while @@fetch_status equals 0. …

The behaviour of a FETCH [ NEXT ] call is that it returns the value(s) for the column(s) specified in the DECLARE CURSOR as a single-row result set. …

Basic cursor example with FETCH returning row as single-row result set
1> declare c1 cursor
2> for
3> select left( name, 32)            as name,
4>        cast( dbid as varchar(10)) as dbid
5>   from master.dbo.sysdatabases
6>  where dbid < 5
8> open c1
9> fetch next from c1
11> -- while @@fetch_status != -1 and @@fetch_status != -2
12> while @@fetch_status = 0
13>    fetch next from c1
15> close c1
16> deallocate c1
18> go
 name                             dbid
 -------------------------------- ----------
 master                           1

 name                             dbid
 -------------------------------- ----------
Read more →

return variables from dynamic sql – sp_executesql

Returing values into tsql variables from a dynamic sql statement using sp_executesql stored procedure. …


The example below displays the number of objects per database. The following steps are involved. …

open a cursor for the select * from master.dbo.sysdatabases statement (cursor is a while loop),
inside the cursor a dynamic sql statement is defined ,
at last the stored procedure sp_executesql is used to retrieve the output of this defined sql statement into tsql variables . …

sp_execute example to return values into variables
1> declare @s_dtb  sysname
2> declare @s_sql  nvarchar(2000)
3> declare @i_obj  int
4> declare c1     cursor
5> for
6> select name
7>   from master.dbo.sysdatabases
10> open c1
11> fetch c1 into @s_dtb
13> while @@fetch_status >= 0
14> begin
16>   -- construct sql statement
17>   set @s_sql = 'select @count = count(*)
18>                  from ' + QuoteName(@s_dtb) + '.dbo.sysobjects'
20>   -- execute dynamic sql and map output variable @count to variable @i_obj
21>   exec sp_executesql
22>     @s_sql,
23>     N'@count int output',
24>     @count = @i_obj output
26>   print @s_dtb + ' db has ' + cast( @i_obj as varchar) + ' objects'
28>   fetch c1 into @s_dtb
30> end
32> close c1
33> deallocate c1
36> go
master db has 1324 objects
model db has 54 objects
msdb db has 454 objects
Northwind db has 146 objects
tempdb db has
Read more →


Getting around in the mssql environment …


In MSSQL (2000) there’s not an easy way to see in which database you are. Script below creates a view ‘whoami’ that gives you the information; …

create view dbo.whoami
select @@spid as '@@spid',
       cast( user as varchar(12)) as 'user',
       cast( p.dbid as varchar(6)) + ':' + cast( as varchar(14)) as 'database',
       cast( p.loginame as varchar(16)) loginame,
       cast( p.hostname as varchar(16)) as 'hostname',
       cast( p.nt_username as varchar(16)) nt_username
   from master.dbo.sysprocesses p left join master.dbo.sysdatabases d
      on d.dbid = p.dbid
   where spid = @@spid


grant select on dbo.whoami to public

1> select * from whoami
2> go
 @@spid user         database              loginame         hostname         nt_username
 ------ ------------ --------------------- ---------------- ---------------- ----------------
     51 dbo          1:master              NYC01\\remivisser NYC01            remivisser

(1 row affected)

Read more →

Date Time functions

Date time functions (frustrations) in T-SQL. …

Current date

Functions getdate and getutcdate display the current date. Both functions accept zero arguments. …

1> print getdate()
2> print getutcdate()
3> go
Sep 18 2006  8:25PM
Sep 18 2006  6:25PM

To format the display of your date you need to convert it to a varchar (like the to_char function in oracle). …

So to display 18 september as 25-09-2006 I must convert it the Italian?? way, as shown below. …

1> print convert( varchar(32), getdate(), 105)
2> go
1> print convert( varchar(32), getdate(), 5)
2> go
Advanced(?) formatting

My requirement is simple: get the date displayed as ‘yyyymmdd_hhmiss’. And yes I want leading zeros for the first nine monts/minutes. So ’09’ for ‘September’ and not ‘9’. How hard can that be … Well, I am missing something bigtime OR this basic functionality is simply missing from sqlserver T-SQL (version 2000). …

I wrote the T-SQL code below to get me the datetime in the format ‘yyyymmdd_hhmiss’; …

1> set nocount on
3> declare @yy varchar(4)
4> ,       @mm varchar(2)
5> ,       @dd varchar(2)
6> ,       @hh varchar(2)
7> ,       @mi varchar(2)
8> ,       @ss varchar(2)
9> ,       @ts varchar(15)
11> set @yy = datepart( yy, getdate())
12> set @mm = datepart( mm, getdate())
13> set @dd = datepart( dd, getdate())
14> set @hh = datepart( hh, getdate())
15> set @mi = datepart( mi, getdate())
16> set @ss = datepart( ss, getdate())
19> if (
Read more →

MSSQL cheat sheet for the Oracle minded

A translation of oracle dba_* tables/v$* views/functions to their respective mssql counterparts. …

dba_* tables

Oracle Table/View
MSSQL Table/View
Note …

dba_data_files (and v_$logfile)
Contains datafiles AND logfiles …


master.dbo.sysxlogins (T)
master.dbo.syslogins (V)

Containts users that belong to <database>. Subset of users in master.dbo.sysxlogins …

Read more →

SQL Trace Reference

Display/Alter running traces or an alternative way to shutdown your server (when ‘c2 audit mode’ is enabled). …

Display running traces

When ‘c2 audit mode’ is enabled you will see a trace running at all times. Whenever the server can’t write to it’s trace file it will shut itself down. (You need to restart with the -f switch to fix your problem). …

Example below first displays all running traces and next aborts the trace ‘facilitating’ the ‘c2 audit mode’ option. The following functions are used, ::fn_trace_getinfo (displays information about runing traces) and sp_trace_setstatus (used to alter the status of a trace). …

1> exec sp_configure "c2 audit mode"
2> go
 name                                minimum     maximum     config_value run_value
 ----------------------------------- ----------- ----------- ------------ -----------
 c2 audit mode                                 0           1            1           1

1> select traceid, property, cast(value as char(64)) as value from ::fn_trace_getinfo(0)
2> go
 traceid     property    value
 ----------- ----------- ------------------------------------------------------------------------
Read more →


Display or change global configuration settings for your mssql server through sp_configure. …

Show (advanced) configuration options

To display the current configuration settings execute dbo.sp_configure without arguments. …

C:\>osql -E
1> exec dbo.sp_configure
2> go
 name                                minimum     maximum     config_value run_value
 ----------------------------------- ----------- ----------- ------------ -----------
 allow updates                                 0           1            0           0
 Cross DB Ownership Chaining                   0           1            0           0
 default language                              0        9999     
Read more →