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 … …

DECLARE
	@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'',  
		NOUNLOAD,  REPLACE,  STATS = 10, RECOVERY'
	exec (@sql)
go



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 →

Control Structures

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

IF THEN ELSE

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 THEN
if 1 = 1
begin 
  print 'true'
end

go

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

go

IF THEN ELSE
if 1 = 1
begin
  print 'true'
end

else  
 
begin
  print 'false'
end

go

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

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

go

CASE

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
  end

print @r


go


WHILE Eternal loop
while 1 = 1 
  print 'true'

go

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

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

go

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
7>
8> open c1
9> fetch next from c1
10>
11> -- while @@fetch_status != -1 and @@fetch_status != -2
12> while @@fetch_status = 0
13>    fetch next from c1
14>
15> close c1
16> deallocate c1
17>
18> go
 name                             dbid
 -------------------------------- ----------
 master                           1

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

return variables from dynamic sql – sp_executesql

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

Scenario

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
8>
9>
10> open c1
11> fetch c1 into @s_dtb
12>
13> while @@fetch_status >= 0
14> begin
15>
16>   -- construct sql statement
17>   set @s_sql = 'select @count = count(*)
18>                  from ' + QuoteName(@s_dtb) + '.dbo.sysobjects'
19>
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
25>
26>   print @s_dtb + ' db has ' + cast( @i_obj as varchar) + ' objects'
27>
28>   fetch c1 into @s_dtb
29>
30> end
31>
32> close c1
33> deallocate c1
34>
35>
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 →

environment

Getting around in the mssql environment …

Whoami

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
as
select @@spid as '@@spid',
       cast( user as varchar(12)) as 'user',
       cast( p.dbid as varchar(6)) + ':' + cast( d.name 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

go

grant select on dbo.whoami to public
go

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

(1 row affected)
1>

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
Formatting

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
18-09-2006
1> print convert( varchar(32), getdate(), 5)
2> go
18-09-06
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
2>
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)
10>
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())
17>
18>
19> if (
Read more →