Tag Archives: Automation

Joining split files through the OS a classic command example of command line flexibility

If you frequently download large files in many parts then you are probably not a stranger to HJSplit. This application is used to connect these parts back together and is quite frankly completely unnecessary! So how do you do it?

Lets say you have just downloaded the following files:-

big_file_part.iso.001
big_file_part.iso.002
big_file_part.iso.003

To join these into one big one:-

Windows

copy /b big_file_part.iso.00? big_file_part.iso

Linux

cat big_file_part.iso.00? > big_file_part.iso

Note the /b switch in the Windows command – this is important because it forces a binary copy. Its so simple why bother with that third party app πŸ™‚

Generating GRANT statements because its quicker

Just a very quick post here, but sometimes when you wish to grant execute right for multiple stored procedures to a particular database user it is easier to simply run the following script in the respective database in order to generate this assignment script. This is far easier than using the 2005 and 2008 GUI interface (and even 2000/ 7) especially if there are a lot of procedures.

USE
SELECT β€˜GRANT EXEC ON .’ + name + β€˜ TO [user]β€˜ 
FROM sysobjects WHERE xtype = β€˜P’ AND

simplifying killing spids because you really ought to

Wow, nearly two months have passed since my last post. Just goes to show how quickly things move on if you get distracted. I’ve been finding it a real problem of late to get the time to update the blog, since I know that when ever I do have something to write, it is going to take up a sizeable portion of time that I do not currently have. I am guessing the only way around this is to keep it short or have regular update slots set aside in my working schedule. Perhaps the latter idea is the best solution.

Moving swiftly on, one problem that I have found particulary annoying when performing any database operation that requires connections to it to be disconnected, such as setting it offline or a restoring over the top of itself is getting rid of them. Obviously this can be achieved by listing the current spids connected to the database and performing a kill for each spid. This can be quite laborious and obvious does not address any spids coming live whilst you are busy killing the existing ones, so you can end up going around in circles.

My solution to this is a script to effectively automate this manual process so that whilst the SET OFFLINE or RESTORE operation or such like are ticking away the procedure can be run specifying the name of the database and hey presto all live connections to this database are killed off one by one. The clever part (imo) to this script is the way in which it doesn’t attempt to block kill a batch of spids at the same time which might not be desirable should one of these spids close and be reused, but instead takes the first spid in the list and kills that and so on until no more spids are active for the specified database.

Obviously it is possible when altering a database to specify the option “WITH ROLLBACK IMMEDIATE” which has the same effect, but this option is not available for every situation and operations.

Anyway here is the stored procedure main body :-

@dbname sysname, –required option (the database from which to kill of any connected user based spids)
@help bit = 0 –optional option (view help)
WITH ENCRYPTION
AS
SET NOCOUNT ON

IF @dbname NOT IN (SELECT name FROM master..sysdatabases) AND LEN(@dbname) = 0
BEGIN
PRINT @dbname + ‘ database specified cannot be found.’
PRINT ”
RETURN
END

IF LOWER(@dbname) IN (‘master’,’tempdb’)
BEGIN
PRINT @dbname + ‘ invalid operation on database ‘ + @dbname + ‘ specify different database.’
PRINT ”
RETURN
END

SET NOCOUNT ON
DECLARE @spid SMALLINT
DECLARE @str VARCHAR(255)

–just select the last spid and kill it and loop around until no more spids
PRINT ‘Killing user processes in database ‘ + @dbname
PRINT ‘Executing from SPID ‘ + CONVERT(VARCHAR,@@SPID)
WHILE EXISTS(SELECT TOP 1 spid FROM master..sysprocesses WHERE dbid = DB_ID(@dbname) and spid > 50 and spid @@SPID) –user spids are 51 and higher
BEGIN
SELECT TOP 1 @spid = spid
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname) and spid > 50 and spid @@SPID –user spids are 51 and higher

SET @str = ‘KILL ‘ + CONVERT(VARCHAR, @spid)

PRINT @str;

EXEC(@str)
END

PRINT ‘Complete.’

auto generating statements every dba should try one

Have you ever performed a scripted restore or some of similar operation and then got entirely frustrated at the laborious and error prone nature of this exercise especially when the database consists of many datafiles and transaction log files?

Of course the SQL Server 2005 GUI is very good at making the restore process much easier but it’s biggest failing is that it doesn’t preserve filenames (especially when the restored database name is changed). This is a very big problem when you have a large number of data files and causes a big headache.

My solution is to use TSQL to auto generate a restore script, and I have found this to work very well and is much faster and more flexible than the GUI approach. When performing operations such as setting up a database mirror (since this can be a more repetitive process AND it is fairly important to preserve file paths) this code really becomes quite useful.

--Ensure that connection is set to source database to
--generate restore script from
DECLARE @backupfilefull SYSNAME

--set path to source database backup file
SET @backupfilefull = 'C:\backuppath\backupfile.bak'

DECLARE @@cmd VARCHAR(4000)

--generate restore statement
SET @@cmd = 'RESTORE DATABASE ' + db_name() + ' FROM DISK = '''
+ @backupfilefull + ''' WITH STATS = 1, NORECOVERY'
SELECT @@cmd = @@cmd + CHAR(10) +',MOVE ''' + RTRIM(name) + ''' TO '''
+ RTRIM(filename) + '''' FROM sysfiles

--output statement
PRINT @@cmd

Running this under the context of the master database would generate the following script:-

RESTORE DATABASE master FROM DISK = 'C:\backuppath\backupfile.bak'  WITH STATS = 1, NORECOVERY
,MOVE 'master' TO 'D:\sql2005\MSSQL.1\MSSQL\DATA\master.mdf'
,MOVE 'mastlog' TO 'D:\sql2005\MSSQL.1\MSSQL\DATA\mastlog.ldf'

I have created similar auto generation scripts for creating database snapshots and various other things which makes life SO much easier. You get the idea!