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

(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

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

  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
  --cycle through the list and kill each spid
  Print @processid
  Print 'killed'
  Exec ('
  kill '+@processid+'
 FETCH FROM processes INTO @processid
CLOSE processes
DEALLOCATE processes

Script to list Simple-recovery-mode databases.

— simple_mode.sql
— script to list Simple-recovery-mode databases

declare @ver varchar(50)
set @ver = (select @@version)

— SQL 2000
if @ver like ‘%2000%’
select name from sysdatabases where status = ’24’ — simple, excluding tempdb

— SQL 2005/8
select name simple_mode from sys.databases where recovery_model = 3 — (3=Simple 1=Full)

Enabling the ‘Dedicated Administrator Connection’

You need to have pre-enabled DAC before you can use this feature (that’s emergency access to SQL-Server-Management-Studio for Administrators), oh and it only seems to work to default instances. Firstly connect as normal to SSMS

– right-click on the server/instance and choose ‘facets’
– choose the bottom facet ‘Surface Area Configuration’
– change ‘RemoteDacEnabled’ to ‘True’ and click OK
– close SSMS

To test
– open SSMS on the local machine
– Click the Cancel button
– click the ‘x’ to close Object Explorer
– choose File / New / Database Engine Query
– prefix the Server name with ‘admin:’ and click Connect.

You can now run commands to troubleshoot the problem, but without help from the Object Explorer window.

Manually Resizing SQL files

The easy way to calculate what size of datafile would have 40% free …
– using the GUI choose Tasks / Shrink / Files
– if the percentage free is less than 40%
– find the last numeric value on the page. EG: “MB (Minimum is 998 MB)”
– multiply this value by 1.6. EG: 1593
– resize the datafile to 1593 or better, IE: 1600 MB

(btw, on this subject – my best-practice is to adjust the increment-size so there is about one ‘autogrowth’ each week).

What physical box am I on?

To find out the Windows Server Name that is currently running the live node use

begin try
exec xp_cmdshell 'ping -a localhost'
end try

begin catch
exec sp_configure 'show advanced options', 1 reconfigure -- to show the advanced options
exec sp_configure xp_cmdshell, 1 reconfigure -- to enable the command-shell
exec xp_cmdshell 'ping -a localhost' -- run a dos-command
exec sp_configure 'xp_cmdshell', 0 reconfigure -- to disable the command-shell
exec sp_configure 'show advanced options', 0 reconfigure  -- to hide the advanced options
end catch

A future proof backup plan.

Yup, I’m really getting to like SQL 2008. How’s this for two backup plans? The first completes a full backup of every database (system included) every 1am. The second backs up the transaction-logs of every database every 4 hours, from 00:45.

The beauty is, plan-1 will automatically incorporate any new databases that the developer cares to create. Similarly plan-2, backs-up every database’s transaction log, ignoring those in Simple recovery mode.

First look at SQL 2008

My first look at sql 2008 (standard edition, 64 bit, on Windows 2008) was … mixed.

First thing I wanted to do was set-up a full daily on-disk backup. I found I was unable to backup to a remote server IE: ‘\[ipaddress]backup$’, as I would get ‘access denied’. Further, I was then unable to edit the maint-plan as the Modify option from the context menu didn’t seem to work. For now I just deleted the plan and made a new one that backed up locally.

On the brighter side I like the ‘activity monitor’ with the row of graphs at the top. Influenced by Oracle me thinks.

Additionally I found the new object-explorer-details panel a joy. Although its really just a re-working of the old sql2000 ‘Taskpad’ view.

So, my initial impression of sql2008 is that its fresh, reflective of current fashion, and a bit buggy.