SQL Server 2008/2012: Automate restore all transaction log backup files

Hi,
It's been a long time since I posted something on this blog. Had been working a lot on the IBM DB2 databases for the last couple of years. So it was business as usual in SQL Server. Hence, nothing noteworthy in SQL Server to blog about. But here's one script that saved me several hours yesterday.

On one of my servers we had to recover 10 databases using last night's full backup and all subsequent log backups. So in the middle of the night it was going to be huge challenge to manually pick every log backup file to restore.

So wrote this script to create commands to RESTORE log backup files which can be then run in a query window.

Please follow the instructions below to use the script.

1) Set the database name to the variable @dbname
2) Copy all the transaction log backups to a new directory. You can choose to start copying the files
    that  were taken a few minutes before the full backup was taken. You can choose to copy the last
    file you want to be restored based on the recovery point of time.
3) In the following line set the directory where you copied the log backup files to.

    insert into #dir
    exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'

4) In the following line set the directory to where the log backup files have been copied.
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM  DISK = 
        N''C:\Backup\'+@filename+''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
select (@cmd)

5) Copy the output which should have the RESTORE commands and run them in a new query    
    window.
6) Lastly run the following command to bring the database out of restoring state.

RESTORE DATABASE dbname WITH RECOVERY

############################################################################
use master
set nocount on
DECLARE @filename varchar(2000), @cmd varchar(8000), @dbname varchar(100)

-----------------------Enter the Database name in the next line
SET @dbname='New'

IF  EXISTS (SELECT name FROM tempdb.sys.tables WHERE name like '#dir%') 
begin
   DROP table #dir
end
create table #dir (filename varchar(1000))

-----------------------Enter the path to TRN File in the xp_cmdshell line
insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'

delete from #dir where filename is null
DECLARE filecursor CURSOR FOR 
select * from #dir order by filename asc

OPEN filecursor
FETCH NEXT FROM filecursor INTO @filename 

WHILE @@FETCH_STATUS = 0 
BEGIN
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM  DISK = N''C:\Backup\'+@filename+''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
print @cmd
FETCH NEXT FROM filecursor INTO @filename
END 
CLOSE filecursor  
DEALLOCATE filecursor
drop table #dir

############################################################################

SQL Server 2005/2008: Get row counts on all the tables.

If you have to count the number of records on all the tables then there are two ways to do that. The first one is the more efficient one and you have to query the system tables to get that info.


SELECT  SCHEMA_NAME(t.schema_id) AS Schema_Name, t.name AS Table_Name, 
i.rows as Row_Count
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
order by i.rows desc

But if you want to SELECT count(*) from all the tables then use the following query.


CREATE TABLE #counts
(     table_name varchar(255),
    row_count int )
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT table_name, row_count FROM #counts ORDER BY  row_count DESC
DROP table #counts

I prefer the first method since that does not involve selecting from each table.

How to script the SQL Server Agent Operators?

As part of the Disaster recovery procedures, I wanted to script out every server object that we had created. This included SQL Server jobs, logins, operators, linked servers and proxies. I was able to script out everything but the SQL Server Agent operators.

After several unsuccessfull Google searches my colleague found the following script to do this.

USE msdb
set nocount on;

create table #tbl (
id int not null,
name sysname not null,
enabled tinyint not null,
email_address nvarchar(100) null,
last_email_date int not null,
last_email_time int not null,
pager_address nvarchar(100) null,
last_pager_date int not null,
last_pager_time int not null,
weekday_pager_start_time int not null,
weekday_pager_end_time int not null,
Saturday_pager_start_time int not null,
Saturday_pager_end_time int not null,
Sunday_pager_start_time int not null,
Sunday_pager_end_time int not null,
pager_days tinyint not null,
netsend_address nvarchar(100) null,
last_netsend_date int not null,
last_netsend_time int not null,
category_name sysname null);

insert into #tbl
  EXEC sp_help_operator;

select 'USE msdb;' + char(13) + char(10) + 'if exists (select * from dbo.sysoperators where name =' + quotename(name, char(39)) + ') ' + char(13) + char(10) +
'exec sp_add_operator ' +
'@name = ' + quotename(name, char(39)) + ', ' +
'@enabled = ' + cast (enabled as char(1)) + ', ' +
'@email_address = ' + quotename(email_address, char(39)) + ', ' +
case
when pager_address is not null then '@pager_address = ' + quotename(pager_address, char(39)) + ', '
else ''
end +
'@weekday_pager_start_time = ' + ltrim(str(weekday_pager_start_time)) + ', ' +
'@weekday_pager_end_time = ' + ltrim(str(weekday_pager_end_time)) + ', ' +
'@Saturday_pager_start_time = ' + ltrim(str(Saturday_pager_start_time)) + ', ' +
'@Saturday_pager_end_time = ' + ltrim(str(Saturday_pager_end_time)) + ', ' +
'@Sunday_pager_start_time = ' + ltrim(str(Sunday_pager_start_time)) + ', ' +
'@Sunday_pager_end_time = ' + ltrim(str(Sunday_pager_end_time)) + ', ' +
'@pager_days = ' + cast(pager_days as varchar(3)) + 
case
when netsend_address is not null then ', @netsend_address = ' + quotename(netsend_address, char(39))
else ''
end +
case
when category_name != '[Uncategorized]' then ', @category_name = ' + category_name 
else ''
end +
'; ' + char(13) + char(10) + 'go'
from #tbl order by id;

drop table #tbl;

SQL Server 2005/2008 : Export Blob or Image datatype from database.

Please refer to my previous blog post on how insert blob or images into SQL Server.

http://saveadba.blogspot.com/2012/02/import-blob-images-tsql.html

There are a few VB scripts to extract or export blob/image data from SQL Server but I prefer the TSQL method. You need to enable the 'Ole Automation Procedures' sp_configure option. This option is disabled by default.

Found a nice script at the following location
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754

To enable Ole automation Procesdures use the following commands.

exec sp_configure 'show_advanced_options', 1
Reconfigure
go

exec sp_configure 'Ole Automation Procedures',1
Reconfigure
go


Use the following TSQL to extract all the blobs or images.
DECLARE @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR
--The following line will select which record/image you want to extract.
--So change the following line to select your record.
SELECT document from Images where id=15
       
OPEN IMGPATH

FETCH NEXT FROM IMGPATH INTO @IMG_PATH

WHILE @@FETCH_STATUS = 0
    BEGIN
        --Set the path where you want to save the file and also the extension of the file.
        --The file name will be the timestamp at which it was extracted.
        SET @TIMESTAMP = 'C:\Pictures\Test\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.JGP'

        PRINT @TIMESTAMP
        PRINT @SQLIMG

        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

        FETCH NEXT FROM IMGPATH INTO @IMG_PATH
    END

CLOSE IMGPATH
DEALLOCATE IMGPATH

If you have saved the file name, extension and the original path in the table then use that in the @TIMESTAMP variable to set the path and file name where you want to save it.

Execute same query against all Databases or table

How to execute multiple queries against all databases?
Running queries against all the databases?

On several occasion I have had a requirement to run a query against each database or each table, or each index. As long as I can query any system table and create a list of objects I want to run the query for, then you can use the following script.
                                         

DECLARE @Database VARCHAR(255)
DECLARE @cmd varchar(1000)


DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.sys.databases  
WHERE state_desc='ONLINE' and name NOT IN ('master','model','msdb','tempdb','distribution','ReportServerTempDB')   
ORDER BY 1  


OPEN DatabaseCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 
     select 'Current database name is : ', @Database
SET @cmd='use ['+@Database+']; Select * from sys.tables'
EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

You can change the script at the line "SELECT name FROM master.sys.databases " and select anything else you want. Example you want to get a row count on each table then replace that SELECT with

select name from sys.tables 

and the cursor will go through each table.
Now inside the WHILE loop use any command you want. In this case you can have

BEGIN
            Select 'Current Table name is :', @Database
            SET @cmd='select count(*) from '+@Database
            Exec (@cmd)


Obviously you can change the reference to @Database to @Table or anything other object that you want to use. I find this method to be easier than using msforeachdb and msforeachtable since I can run it for all indexes, columns or anything else I want.

SQL Server 2005/2008 : Import Blob or images using TSQL

SQL Server 2005/2008: Import images using TSQL
SQL Server 2005/2008: Import blob using TSQL

There are several options to store BLOB or images data type in SQL Server database. Also there are several options in insert and export BLOB or image data types. But I always prefer the TSQL methods since I can write stored procedures and use those. There are some VB Script options too so import and export BLOB/Images into SQL Server database.

First lets look at a table that has a BLOB datatype column
CREATE TABLE [dbo].[Images](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [document] [image] NULL)

To insert images/blob data into SQL Server 2005 and 2008 use the following query

INSERT INTO Images(document)  

SELECT * 

FROM OPENROWSET(BULK N'C:\Pictures\IMG_1.jpg', SINGLE_BLOB) as tempImg

If you have multiple files that you want to insert then use a cursor to go through each file and insert into the table.

DECLARE @imgString varchar(80), @imgNumber int
DECLARE @insertString varchar(3000)

SET @imgNumber = 1
WHILE (@imgNumber < 14)
BEGIN
    SET @imgString = 'C:\Pictures\IMG_' + CONVERT(varchar,@imgNumber) + '.jpg'
    SET @insertString = N'INSERT INTO Images(document)
                        SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'
    EXEC(@insertString)
    SET @imgNumber = @imgNumber + 1
END

I have had difficulty in extracting the filename and the extension once it is inserted into the table. So I highly recommend that the table have a column for the file name and path where it was imported from. And these values be inserted into the table. So create a table like this.

CREATE TABLE [dbo].[Images](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Filename] varchar(100),

    [Path] varchar(200),

    [document] [image] NULL    )

This way when you extract it then you do not have to worry about what the destination filename should be and what was the extension of the file.

SQL Server : ActiveX component can’t create object: ‘SQLDMO.SQLServer’

Recently I uploaded a VB script to generate the DDL for all the jobs. But when I ran the same VB Script against a SQL Server 2008 instance I got the following error.

Activex component can’t create object: ‘SQLDMO.sqlserver’ on SQL Server 2008

Error: activex component can’t create object: ‘SQLDMO.sqlserver’

Code: 800A01AD

Source: Microsoft vbscript runtime error

Upon further investigation I found that this is caused due to fact that on SQL Server 2008 SQLDMO is not installed by default and the best workaround is to install the Microsoft SQL Server 2005 Backward Compatibility Components.

This could be useful if you have other VB Scripts that were running until you migrated to SQL Server 2008.

But I would also recommend to start working on a new script that does not need SQLDMO. 

SQL Server 2005/2008 : Generate scripts all jobs

SQL Server 2005: How to generate scripts for all jobs?
SQL Server 2005: Script all jobs

There are some TSQL methods that will generate the scripts to create all jobs but there is one major constraint with those scripts.

They all use a variable like @command which is usually nvarchar or varchar and this limits the number of characters in the job step. If you have a large TSQL in the job step then you can not use those TSQL queries to script out all job.

The best method to do this is using a VB Script that I found online. I am not the author of the script but wanted to list it here.

Dim conServer
Dim fso
Dim iFile
Dim oJB
Dim strJob
Dim strFilename
Const ioModeAppend = 8

Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect "yourservername"

strFilename = "C:\DR_Files\CreateJobs.sql"

For Each oJB In conServer.JobServer.Jobs
    strJob = strJob & "--------------------------------------------------" & vbCrLf
    strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
    strJob = strJob & "--------------------------------------------------" & vbCrLf
    strJob = strJob & oJB.Script() & vbCrLf
Next
Set conServer = Nothing

Set fso = CreateObject("Scripting.FileSystemObject")
Set iFile = fso.CreateTextFile(strFilename, True)
iFile.Write (strJob)
iFile.Close
Set fso = Nothing

Save this to a file with the extension 'vbs' and execute it on a command line or via a command line job step using the following command. Make sure you edit the file to use 'yourservername' and also change the path to the output file. In the above example I am saving the file to C:\DR_Files\CreateJobs.sql.

C:\WINDOWS\system32\cscript.exe C:\DR_Files\Generate_Jobs.vbs

This should create a file that can be used to create all the jobs. Make sure you parse the file in a query window to make sure you can create the scripts. But DO NOT EXECUTE the output file.

SQL Server 2005/2008 ; Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

SQL Server 2005/2008


Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[v_errorlog]
WITH SAMPLE 50 PERCENT,NORECOMPUTE

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The problem is due to the fact that the maintenance plan was trying to update the stats on views too. And on a view we can not update the stats with anything other than full scan.
The maintenance plan was configured to run update stats on all the databases. But when you do that, you do not get the choice to pick if you want to exclude or include tables/views or specific tables/views.

It pretty much runs the plan against all DBs, all tables and all views. You can exclude the views only if you select one database at a time. If you have limited number of databases in the instance then create a maintenance plan like the following.

1)       Create two ‘Update statistics’ tasks for each database.
2)       The first task for each database will update the statistics only on tables. You can choose the  
           sampling percentage in this step.
          
3)       The second task  for each database will update the statistics only on views. But make sure you 
           pick full scan option in this step.
4)       Repeat step 2 and 3 for each database.

If it is not possible to create multiple tasks due to the large of databases in the instance, then you will have to write a script to this. If you need help then let us know.

Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.

I was trying to move a database from one server to the other. After successfully restoring the database. I ran the orphaned users report and found a couple of users and I fixed them by creating new logins for them.

The user dbo was being reported as an orphaned user and if tried to run the command to fix it I would get the following error.

Server: Msg 15287, Level 16, State 1, Line 1
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.

The fix for this error is to run the following command.

Exec sp_changedbowner 'sa' 



If you are still getting a list of orphaned users then do the following.
1) This will lists the orphaned users:
    EXEC sp_change_users_login 'Report'

2) If you already have a login id and password for this user, fix it by running following command for    each user

    EXEC sp_change_users_login 'Auto_Fix', 'user'

3) If you want to create a new login id and password for this user, fix it by running the following command for each user. Make sure you assign the password and give that to the user.

  EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

SQL Server : Get CPU consuming query

On certain occasions you will have to troubleshoot high CPU consumption on a server. A rogue query would start chewing up CPU resource and you will see that sqlserver.exe will be using up all the CPU. 

Before running a profiler trace to see which queries are consuming more CPU resources than others, run the following TSQL to capture the SPID that is consuming most of the CPU. In the script I have given an interval of 5 secs and if you want you can use an interval on 10 or 15 secs too.

create table #cpu2 (spid int, dbid int, cpu bigint)

create table #cpu (spid int, dbid int, cpu bigint)

insert into #cpu

select spid, dbid, cpu from sysprocesses order by cpu desc

WAITFOR DELAY '00:00:05'

insert into #cpu2

select spid, dbid, cpu from sysprocesses order by cpu desc

select * from sysprocesses where spid in (select t.spid from #cpu2 t, #cpu o 

where t.cpu> o.cpu and t.spid=o.spid) order by cpu desc

drop table #cpu2

drop table #cpu

Run the DBCC inputbuffer with the SPID at the top of the results and you will find the query that is chewing up all the CPUs.

If you want to select the top 10 queries that are currently consuming more CPU than others then use the top 10 SPIDs to find the queries.

This is mostly useful if you have one or two rogue queries. If you have a set of queries that you doubt are using up more resources then run the profiler trace to capture them in real time.

SQL Server : Execute same query against each database or table

How to execute multiple queries against all databases?
Running queries against all the databases?

On several occasion I have had a requirement to run a query against each database or each table, or each index. As long as I can query any system table and create a list of objects I want to run the query for, then you can use the following script.
                                         

DECLARE @Database VARCHAR(255)
DECLARE @cmd varchar(1000)


DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.sys.databases  
WHERE state_desc='ONLINE' and name NOT IN ('master','model','msdb','tempdb','distribution','ReportServerTempDB')   
ORDER BY 1  


OPEN DatabaseCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 
     select 'Current database name is : ', @Database
SET @cmd='use ['+@Database+']; Select * from sys.tables'
EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

You can change the script at the line "SELECT name FROM master.sys.databases " and select anything else you want. Example you want to get a row count on each table then replace that SELECT with

select name from sys.tables 

and the cursor will go through each table.
Now inside the WHILE loop use any command you want. In this case you can have

BEGIN
            Select 'Current Table name is :', @Database
            SET @cmd='select count(*) from '+@Database
            Exec (@cmd)


Obviously you can change the reference to @Database to @Table or anything other object that you want to use. I find this method to be easier than using msforeachdb and msforeachtable since I can run it for all indexes, columns or anything else I want.

Get blocking query

If you want to capture the SPID and the query that is causing blocking then refer to my previous post
http://saveadba.blogspot.com/2012/02/find-query-causing-blocking-extended.html


But I have  come across situations where I am troubleshooting for extended blocking. But unfortunately the queries causing the extended blocking finish execution before I can login to the server. So I have written a script which will capture all the information that I need. I save the output to a file via a job and I email the output to myself. This way I have a record of the queries that cause extended blocking.

I have set up the following script in a job and have configured the job to save the output to a file. Following is a sample of the output which tells me which query is causing blocking and which one is being blocked.


Query #1
run by spid : 62
This is the SPID that is causing the blocking
 hostname                            loginame
--------
SQLServerprd01          Appuser


--------
 Running DBCC INPUTBUFFER for SPID 62
EventType Parameters EventInfo
--------- ---------- ---------
Language Event 0  insert into test...........

Use the following script for this.............................................................................................

use master

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
set nocount on
DECLARE @RefCount varchar(100)
DECLARE @sql1 VARCHAR(4000),@sql2 VARCHAR(4000)
DECLARE @querynum varchar(100)
DECLARE @sqlcmd varchar(8000)
DECLARE @request_status varchar(50)
SET @querynum=1


DECLARE RefCursor CURSOR FOR


SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))


OPEN RefCursor
FETCH NEXT FROM RefCursor INTO @RefCount
WHILE @@FETCH_STATUS = 0
BEGIN
Select 'Query #'+@querynum+'
run by spid : '+@RefCount
SET @request_status=(SELECT
distinct(l.request_status)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND ( l.resource_description = l1.resource_description OR
(l.resource_description IS NULL AND l1.resource_description IS NULL))
and l.request_session_id=@RefCount)
IF (@request_status='WAIT')
begin
     PRINT 'This is the SPID that is being blocked'
    end
else
begin
     PRINT 'This is the SPID that is causing the blocking'
     end
select ''
SET @sqlcmd='Select hostname from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)


SET @sqlcmd='Select loginame from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)
SET @querynum=@querynum+1
SET @sql2='DBCC INPUTBUFFER('+@RefCount+') with no_infomsgs'
SELECT 'Running DBCC INPUTBUFFER for SPID '+@RefCount
EXEC (@sql2)



SELECT '-----------------------------------------------------------------'


FETCH NEXT FROM RefCursor INTO @RefCount
END
CLOSE RefCursor
DEALLOCATE RefCursor
GO

SQL Server : Backup and restore progress

Ever wondered how long it would take to complete a backup or restore that is currently in progress. Use the following command to find that. It gives you a rough estimate of the amount of time required to complete it.


SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

SQL Server Get status of Backup
SQL Server Get status of Restore
SQL Server Get Backup completion
SQL Server Get Restore completion.