Month: April 2011

Taste the freshness of a restore.

I struggled for ages to find the simplest way to handle a situation where a backup file may or may not have been uploaded to an FTP site for restoring (see my last, which worked fine – but failed to produce the desired error message when the folder was empty).

As it only took a few minutes to restore this particular database, it was acceptable to blindly restore it and then decide if doing so had been useful. For this I made use of the backup_set_id column in the restorehistory table within MSDB.

This column appears to contain a check-sum for every restore, and therefore it was easy enough to see if the last two restores had the same value. Here’s the full job-step code …

select top 2 backup_set_id
into #temp
from restorehistory
where destination_database_name = 'AdventureWorks'
order by restore_history_id desc

if
(select max(backup_set_id) from #temp)
 =
(select min(backup_set_id) from #temp)
exec sp_send_dbmail
 @recipients = 'somebody@somewhere.com',
 @subject = 'AdventureWorks was restored from an old backup';

Using ‘CoreFTP’ command line

To download a backup from an FTP site then delete the original …

C:pathcorecmd.exe -s -O -site site -d /path/latestbackup.bak -p c:ftp -delsrc -log c:logfile.log

-s = silent

-O = overwrite

-site = pre configured connection details within coreftp gui

-d = path to ftp site

-p = local destination

-delsrc = delete source after successful download

-log = record a logfile

******** UPDATE Dec 2012 ********

I found that this utility was the only way I could find to script/schedule FTPS uploads & downloads. Here’s more working examples …

“C:Program Files (x86)CoreFTPcorecmd.exe” -s -OS -site thirdpartyname -u “E:DBAthirdpartynamesomefilename_2012*.txt” -p “/export files/” -log E:DBAthirdpartynamelogfile.log

“C:Program Files (x86)CoreFTPcorecmd.exe” -s -OS -site thirdpartyname -d “/Client imports/somefilename_*.txt” -p \mylocalsqlserverdbathirdpartyname -log \mylocalsqlserverdbathirdpartynamelogfile.log

Note:-
– The use of double quotes so the utility isn’t confused by spaces in the path.
– The -OS switch which means copy everything except what’s already copied.
– The use of wild-cards with -OS to simplify management of file-names containing dates.
– the use of -u for uploading, and -d for downloads.

Best Practice – Creating a Procedure that includes remote references

To create or alter a stored procedure that contains references to data on remote servers you need to connect using an account that can access all the data.

That is, to successfully alter a sp that includes links to 2 remote servers, connect (in SSMS) using an account that is common in the ‘Linked Servers’ configuration. Test this by drilling down through ‘Linked Servers’ to see both lots of tables.

Kill all users

In a technical sense of course 🙂

Here’s the code – very handy before a restore …

DECLARE processes CURSOR FOR
  select spid from sysprocesses where dbid=(select dbid from sysdatabases where name='AdventureWorks') and status in ('runnable','sleeping')
DECLARE @processid int   -- spid of active processes

OPEN processes --open the cursor
 FETCH FROM processes INTO @processid
 WHILE (@@FETCH_STATUS = 0)
  --cycle through the list and kill each spid
 BEGIN 
  Print @processid
  Print 'killed'
  Exec ('
  kill '+@processid+'
  ')
 FETCH FROM processes INTO @processid
 END -- END OF WHILE (@@FETCH_STATUS = 0) 
CLOSE processes
DEALLOCATE processes

Find stored-procedure on current server

A SQL-Job was failing with Could not find stored procedure ‘RemovePushMsgs’.

I noticed the drop-down-value ‘database’ was missing from the job-step – which was odd. To confirm my suspicion that the database had been removed but the job forgotton I wrote the following script to search each and every database for the missing SP.

--find_sp.sql
-- to find stored-procedure names like '%RemoveM%' in all databases

create table #temp (name varchar(50), dbname varchar(50), xtype varchar(50))

exec sp_Msforeachdb "use [?];insert #temp select name, '?', xtype from sysobjects where name like '%RemoveM%'"

select name, dbname, case xtype
when 'P' then 'SP'
when 'S' then 'System Table'
when 'X' then 'XP'
when 'U' then 'Table'
when 'TF' then 'FunctionT'
when 'FN' then 'FunctionS'
when 'V' then 'View'
when 'IF' then 'FunctionI'
when 'D' then 'Default'
else xtype end [type]
from #temp
order by 1

drop table #temp