Nightly Restore from FTP

Similar to a previous project, a customer wanted a SQL-2005 database refreshing over-night from a backup on an FTP server. Using an already inplace tool ‘CoreFTP’ via an already inplace stored-procedure ‘usr_FTPDownload’ I created a job …

Step-1 “Download backup(s) from ftp site”
exec dba.dbo.usr_FTPDownload
@ftpfile = ‘*.bak’,
@DLdir = ‘E:FTP’,
@ftpprofile = ‘FTP’,
@logfile = ‘E:Microsoft SQL ServerMSSQL.1MSSQLLOGRestore of AdventureWorks from FTP.log’,
@email = ‘’ — on failure

Step-2 “Restore the latest backup”
create table #temp (col1 varchar(max))
insert #temp(col1)
exec xp_cmdshell ‘dir /b /o-d e:ftp*.bak’

declare @newest varchar(50)
set @newest = (select top 1 * from #temp)

declare @cmdstr varchar(200)
set @cmdstr = ‘restore database AdventureWorks
from disk=”e:ftp’ + @newest + ”’
with file=1,

exec (@cmdstr)

Step-3 “grant access to user” (running on AdventureWorks)
create user an_other for login an_other
exec sp_addrolemember ‘db_datareader’, ‘an_other’

Step-4 “rename the backup-file for housekeeping”
— get name of latest backup
create table #temp (col1 varchar(max))
insert #temp(col1)
exec xp_cmdshell ‘dir /b /o-d e:sftp*_*.bak’

declare @oldname varchar(50)
set @oldname = (select top 1 * from #temp)

–check if already renamed
if @oldname like ‘%File Not Found%’ return

— extract namepart
declare @namepart varchar(50)
set @namepart = left(@oldname,(patindex(‘%[_]%’,@oldname)-1)) — text before underscore

— convert date-part into day-of-week
declare @weekday varchar(10)
set @weekday = datepart(dw, right(left(@oldname,(patindex(‘%.%’,@oldname)-1)),10))

— buld-up new-name
declare @newname varchar(50)
set @newname = @namepart + @weekday + ‘.bak’

— run it
declare @cmdstr varchar(100)
set @cmdstr = ‘ren e:sftp’ + @oldname + ‘ ‘ + @newname
exec xp_cmdshell @cmdstr

Step-5 “email results – on success”

declare @datetime as varchar(20)
declare @email as varchar(100)

set @datetime = convert(varchar(17),(select top 1 restore_date
from msdb.dbo.restorehistory
where (destination_database_name = ‘AdventureWorks’)
order by restore_date desc),113)

set @email = ‘Database “AdventureWorks” Refreshed at “‘+@datetime+'”.’

exec msdb.dbo.sp_send_dbmail
@recipients = ‘’,
@subject = @email ;

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).