Data Whacking with SQL Server

These stored procedures may really come in handy if you are using one of the Express Editions of Microsoft SQL Server.  Once you have added the stored procedures to your master database, you can use Windows Scheduled Tasks (or your own method of choice) to generate database and transaction log backups on a schedule.

In all cases, replace ‘F:\SQLData\SQLBackup’ with the backup folder of your choice.

Here is a command you can then use to generate the database backups if you are using a 2005 version of SQL Server (for 2000,  replace the \90\ with \80\; for 2008, replace the \90\ with a \100\):
C:\Program Files\Microsoft SQL Server\90\Tools\binn>osql -E -Q “exec [dbo].[usp_BackupAllDatabases] ‘F:\SQLData\SQLBackup’”

Command to generate the transaction log backups:
C:\Program Files\Microsoft SQL Server\90\Tools\binn>osql -E -Q “exec [dbo].[usp_BackupAllTransactionLogs] ‘F:\SQLData\SQLBackup’”

After customizing your database recovery model options in the usp_BackupAllTransactionLogs stored procedure below, you can run this for the first time initialization:

On this first line, you may get errors… ignore them.
C:\Program Files\Microsoft SQL Server\90\Tools\binn>osql -E -Q “exec [dbo].[usp_BackupAllTransactionLogs] ‘F:\SQLData\SQLBackup’”
These two should run without complaining…
C:\Program Files\Microsoft SQL Server\90\Tools\binn>osql -E -Q “exec [dbo].[usp_BackupAllDatabases] ‘F:\SQLData\SQLBackup’”
C:\Program Files\Microsoft SQL Server\90\Tools\binn>osql -E -Q “exec [dbo].[usp_BackupAllTransactionLogs] ‘F:\SQLData\SQLBackup’”

[Please note: I have attached the SQL script shown below here: Gerneric Backup Scripts for SQL Server; posting the scripts to HTML is mangling the punctuation.]

Here are the scripts:

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_what]    Script Date: 08/03/2009 07:50:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_what]
–    @loginame     sysname = NULL
as
/*

http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/31690/

By Andrew Stanton
*/
set nocount on

declare
@retcode         int, @loginame  sysname
declare
@sidlow varbinary(85),@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh int

declare
@charMaxLenLoginName varchar(6),@charMaxLenDBName varchar(6),@charMaxLenCPUTime varchar(10),@charMaxLenDiskIO varchar(10),
@charMaxLenHostName varchar(10),@charMaxLenProgramName varchar(10),@charMaxLenLastBatch varchar(10),@charMaxLenCommand varchar(10)
——–
select  @retcode         = 0      — 0=good ,1=bad.
————————————————————–

if (object_id(‘tempdb..#tb1_sysprocesses’) is not null)
drop table #tb1_sysprocesses

——————–  Capture consistent sysprocesses.  ——————-

SELECT
spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,
convert(sysname, rtrim(loginame)) as loginname,spid as ‘spid_sort’,
substring( convert(varchar,last_batch,111) ,6  ,5 ) + ‘ ‘ + substring( convert(varchar,last_batch,113) ,13 ,8 ) as ‘last_batch_char’,last_batch
INTO    #tb1_sysprocesses
from master.dbo.sysprocesses   (nolock)

——–Screen out any rows
DELETE #tb1_sysprocesses
where   lower(status)  = ‘sleeping’
and     upper(cmd)    IN (
‘AWAITING COMMAND’
,’MIRROR HANDLER’
,’LAZY WRITER’
,’CHECKPOINT SLEEP’
,’RA MANAGER’
,’TASK MANAGER’
)

and     blocked       = 0 or spid <= 50
—set the column widths
UPDATE #tb1_sysprocesses set last_batch = DATEADD(year,-10,GETDATE())
where last_batch IS NULL or last_batch = ’01/01/1901 00:00:00′ or last_batch < ’01/01/1950′
update #tb1_sysprocesses set status = substring(status,1,10), program_name = substring(program_name,1,20)
ALTER TABLE #tb1_sysprocesses
ALTER COLUMN status varchar(10)
ALTER TABLE #tb1_sysprocesses
ALTER COLUMN program_name varchar(20)
——–Prepare to dynamically optimize column widths.
SELECT
@charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5)),
@charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)),
@charMaxLenCPUTime =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)),
@charMaxLenDiskIO =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)),
@charMaxLenCommand = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)),
@charMaxLenHostName  = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)),
@charMaxLenProgramName = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)),
@charMaxLenLastBatch = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
from
#tb1_sysprocesses
where
spid >= 0 and spid <= 32767

——–Output the report.

EXECUTE(
‘SET nocount off
SELECT   SPID          = convert(char(5),spid)
,HostName      =
CASE hostname
When Null  Then ”  .”
When ” ” Then ”  .”
Else    substring(hostname,1,’ + @charMaxLenHostName + ‘)
END
,BlkBy         =
CASE               isnull(convert(char(5),blocked),”0”)
When ”0” Then ”  .”
Else            isnull(convert(char(5),blocked),”0”)
END
,ActiveSeconds = DATEDIFF(ss,last_batch,getdate())
,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,’ + @charMaxLenDBName + ‘)
,Command       = substring(cmd,1,’ + @charMaxLenCommand + ‘)
,Status        =
CASE lower(status)
When ”sleeping” Then lower(status)
Else                   upper(status)
END
,BatchStart = CONVERT(varchar(8),last_batch,14)
,Now = CONVERT(varchar(8),getdate(),14)
,LBDate = substring(last_batch_char,1,5)
,ProgramName   = substring(program_name,1,’ + @charMaxLenProgramName + ‘)
,Login         = substring(loginname,1,’ + @charMaxLenLoginName + ‘)
,CPUTime       = substring(convert(varchar,cpu),1,’ + @charMaxLenCPUTime + ‘)
,DiskIO        = substring(convert(varchar,physical_io),1,’ + @charMaxLenDiskIO + ‘)
from
#tb1_sysprocesses  –Usually DB qualification is needed in exec().
order by CAST(SPID as int)
– (Seems always auto sorted.)   order by SPID
SET nocount on’)

drop table #tb1_sysprocesses
–return @retcode

GO

/****** Object: StoredProcedure [dbo].[usp_BackupAllDatabases] Script Date: 07/31/2009 19:04:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_BackupAllDatabases]
@BackupTarget_FullPathToFolder varchar(4000)
, @UseLitespeed int = 0
as

set nocount on

declare @err int
declare @sql varchar(256)
declare @db_name varchar(128)
declare @BackupName varchar(255)

declare @RunDate varchar(255)
set @RunDate = replace(replace(convert(varchar(255), getdate() , 120), ‘ ‘, ‘_’), ‘:’,”)
—- select @RunDate

– Make sure our path ends in a ‘\’…
set @BackupTarget_FullPathToFolder = ltrim(rtrim(@BackupTarget_FullPathToFolder))
if right(@BackupTarget_FullPathToFolder, 1) <> ‘\’ set @BackupTarget_FullPathToFolder = @BackupTarget_FullPathToFolder + ‘\’

declare extract CURSOR Local FOR

select
[name]
from
sysdatabases
where
version <> 0
and DATABASEPROPERTYEX(name, ‘Status’) = ‘ONLINE’
and DATABASEPROPERTYEX(name, ‘IsInStandBy’) = 0
and [name] <> ‘tempdb’
and [name] = ‘RCRC’
order by
case when [name] = ‘master’ then ‘ZZZ’ else [name] end desc

OPEN extract
FETCH NEXT FROM extract
INTO @db_name

WHILE (@@FETCH_STATUS = 0)
BEGIN

set @BackupName = @BackupTarget_FullPathToFolder + @db_name + ‘_’ + @RunDate + ‘.bak’

if (@UseLitespeed = 1)
begin

– LiteSpeed backup…
exec xp_backup_database @database = @db_name, @filename = @BackupName, @init = 1, @logging = 0, @with = ‘SKIP’, @with = ‘STATS = 10′
end
else
begin
– SQL Server Backup…
set @sql = ‘backup database [' + @db_name + '] to disk = ”’+ @BackupName + ”’ with init’
exec(@sql)
end

select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))

– Shrink Database
– As per Microsoft, avoid shrinking / regrowing the tempdb
–set @sql = ‘DBCC SHRINKDATABASE (N”’ + @db_name + ”’, 75)’
–exec(@sql)

FETCH NEXT FROM extract INTO @db_name
END

CLOSE extract
DEALLOCATE extract

GO

/****** Object: StoredProcedure [dbo].[usp_BackupAllTransactionLogs] Script Date: 07/31/2009 19:04:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_BackupAllTransactionLogs]
@BackupTarget_FullPathToFolder varchar(4000)
, @UseLitespeed int = 0
as
/*
exec [dbo].[usp_BackupAllTransactionLogs] ‘e:\’,0
*/

set nocount on

declare @err int
declare @sql varchar(256)
declare @db_name varchar(128)
declare @BackupName varchar(255)
declare @copy_cmd varchar(255)

declare @RunDate varchar(255)
set @RunDate = replace(replace(convert(varchar(255), getdate() , 120), ‘ ‘, ‘_’), ‘:’,”)
– select @RunDate

– Make sure our path ends in a ‘\’…
set @BackupTarget_FullPathToFolder = ltrim(rtrim(@BackupTarget_FullPathToFolder))
if right(@BackupTarget_FullPathToFolder, 1) <> ‘\’ set @BackupTarget_FullPathToFolder = @BackupTarget_FullPathToFolder + ‘\’

create table #SimpleRecoveryModelDatabases (
dbname varchar(255)
)

– Edit this as you see fit…
insert into #SimpleRecoveryModelDatabases(dbname) values (‘model’)
insert into #SimpleRecoveryModelDatabases(dbname) values (‘msdb’)
insert into #SimpleRecoveryModelDatabases(dbname) values (‘ReportServer’)
insert into #SimpleRecoveryModelDatabases(dbname) values (‘ReportServerTempDB’)
insert into #SimpleRecoveryModelDatabases(dbname) values (‘hmail’)

CREATE TABLE #BackupInProgress (
[SPID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BlkBy] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ActiveSeconds] [int] NULL ,
[DBName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Command] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BatchStart] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Now] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LBDate] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DiskIO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

declare extract CURSOR Local FOR

select
[name]
from
sysdatabases
where
version <> 0
and DATABASEPROPERTYEX(name, ‘Status’) = ‘ONLINE’
and DATABASEPROPERTYEX(name, ‘IsInStandBy’) = 0
and [name] not in (select * from #SimpleRecoveryModelDatabases)
and [name] not in (‘tempdb’,'master’)
and [name] = ‘RCRC’
order by
[name]

OPEN extract
FETCH NEXT FROM extract
INTO @db_name

WHILE (@@FETCH_STATUS = 0) BEGIN

truncate table #BackupInProgress

insert into #BackupInProgress exec sp_what

if ((select count(1) from #BackupInProgress where DBName = @db_name and Command = ‘BACKUP DATABASE’) = 0) — is the current db being backed up now?
begin

– Check on recovery mode…
if @db_name in (select * from #SimpleRecoveryModelDatabases)
begin
set @sql = ‘alter database [' + @db_name + '] set recovery simple’
exec(@sql)
select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end
else
begin
set @sql = ‘alter database [' + @db_name + '] set recovery FULL’ — you can consider BULK LOGGED here as well.
exec(@sql)
select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end

– Backup the transaction logs…
– For TEMPDB (and other SIMPLE), truncate the log…
/* START: This is depricated in 2005 and unsupported in 2008, so comment this out if you are using SQL Server 2008 or newer… */
if DATABASEPROPERTYEX(@db_name, ‘Recovery’) = ‘SIMPLE’
begin
set @sql = ‘backup log [' + @db_name + '] with truncate_only’
exec(@sql)
select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end
else
/* END: This is depricated in 2005 and unsupported in 2008, so comment this out if you are using SQL Server 2008 or newer… */
– For other logging databases, backup the log…
if DATABASEPROPERTYEX(@db_name, ‘Recovery’) <> ‘SIMPLE’
begin

set @BackupName = @BackupTarget_FullPathToFolder + @db_name + ‘_log_’ + @RunDate + ‘.bak’

if (@UseLitespeed = 1)
begin
– LiteSpeed backup…
exec xp_backup_log @database = @db_name, @filename = @BackupName, @init = 1, @logging = 0, @with = ‘SKIP’, @with = ‘STATS = 10′
end
else
begin
– SQL Server Backup…
set @sql = ‘backup log [' + @db_name + '] to disk = ”’+ @BackupName + ”’ with init’
exec(@sql)
end

select @err = @@error
if @err <> 0 print ltrim(rtrim(str(@@error)))
end

end

– Shrink Database
– As per microsoft, avoid shrinking / regrowing the tempdb
–set @sql = ‘DBCC SHRINKDATABASE (N”’ + @db_name + ”’, 75)’
–exec(@sql)

FETCH NEXT FROM extract INTO @db_name

END

CLOSE extract
DEALLOCATE extract

drop table #SimpleRecoveryModelDatabases

GO

Sean SQL Scripts backups, SQL