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 = 'email@example.com',
@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 fix for that annoying bug in SQL 2008 Management Studio which stops job-steps being edited after a few times.
cd C:Program FilesMicrosoft SQL Server100DTSBinn
… and click ‘OK’ to re-register the dll.
A regular index maintenance Job we run started returning an error …
Msg 7999, Level 16, State 40, Line 1
Could not find any index named ‘index_name’ for table ‘table_name’.
… the fix was to drop the statistics for the non-existent index …
drop statistics table_name.index_name
— 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)
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
– 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.
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).
To find out the Windows Server Name that is currently running the live node use
exec xp_cmdshell 'ping -a localhost'
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
Occassionly with SQL 2008 jobs, I find I cannot open the job-step output file. A possible solution is to use xp_cmdshell, for example
exec xp_cmdshell ‘type “C:MSSQL.1MSSQLLOGCheck Backups.txt”‘
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.
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.