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
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)
@recipients = 'firstname.lastname@example.org',
@subject = 'AdventureWorks was restored from an old backup';
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.
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.
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
FETCH FROM processes INTO @processid
END -- END OF WHILE (@@FETCH_STATUS = 0)
Here’s a handy query to list the latest restores on a server …
Select destination_database_name, convert(varchar(20), max(restore_date),113) last_restore
group by destination_database_name
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.
-- 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]
order by 1
drop table #temp