Another year is drawing to an end. That means it is time to review the successes and failures on my list of resolutions. It is also time to make new ones. On the plus side I have already met my commitment to at least one blog post per month average. I won’t have to put up a flurry of articles in the next week and a half to meet that resolution. I think I should modify my resolution next year such that I need to post more regularly.
Reviewing my posts I have noticed that I am following that advice that I post about what interests me.Of course, I have been told that I can be interested in things that don’t necessarily interest others. It’s not that people aren’t interested in database administration or SQL server; It’s that most people aren’t interested in more abstruse topics. At a recent board meeting of the Philadelphia SQL Server user’s group we were discussing speakers and topics. We wanted to be clear that we are seeking to bring in speakers that will bring our members to meetings.
To that effect I am going to stipulate for the coming year’s blog posts that I post about more accessible topics. I am also going to make it a point to articulate why my sometimes more advanced seeming efforts are not. One example comes to mind.
When you are trying to set up an automatic restore job and you want that job to start when the backups have finished. In order for a job to be started via a linked server the linked server account must own that job (it might also need to be a member of the target server role modified to be granted execute on sp_startjob). There must also be a connection such that it can read the backup tables to find the placement of the backup files. XP_dirtree of cmdshell can also be used to list existing files but they won’t fail the way attempting backup files that don’t exist (perhaps they got deleted, it would behoove you to notice this). If we were to use SSIS it would only need to be db_creator on the restore machine and db_reader on msdb on the backup machine, as well as have read file system permissions for the location of the backup. This is a smaller service area than would be needed to start remote jobs and read remotely, especially as it would be a single Windows login as opposed to two SQL Logins, and at least one Windows login (more if the SQL Agent account is not a domain account used by both machines).