October 8, 2010 at 3:19 am
Hi all,
I was after a bit of help if possible. I want to automate the process of copying the last full backup for a particular database to another server so that it can be used for our development team and as such should be run at the server where the restore is going to happen rather than the source.
Here are the steps of the job as I see it:
1) Run a script of some kind to identify last backup file (each backup file contains only 1 backup) and so can be identified by date created. This file should be copied and renamed so the restore step does not have to be dynamic.
2) RESTORE DATABASE - I have no problems with this part.
3) Notify operators - again no problems here.
XP_Cmdshell is not enabled on any of our servers and apparently enabling it is not an option. Ideally something in powershell would be nice as I'd like to take a look at that but ActiveX will do.
The servers in question are SQL Server 2008 sitting on W2K8.
Any help would be greatly appreciated.
October 8, 2010 at 3:38 am
Under SQL Server Agent, when you create a job step, you can specify the "Type" which can be Operating system (CmdExec). Might be worth a try... however, I am not sure if it will (not) work without xp_cmdshell.
On a side note... you have PowerShell enabled, but not xp_cmdshell :hehe:
October 8, 2010 at 3:44 am
Have you considered using SSIS? It has built-in file manipulation tasks, so it should be just what you need.
John
October 8, 2010 at 4:02 am
grahamc (10/8/2010)
Under SQL Server Agent, when you create a job step, you can specify the "Type" which can be Operating system (CmdExec). Might be worth a try... however, I am not sure if it will (not) work without xp_cmdshell.On a side note... you have PowerShell enabled, but not xp_cmdshell :hehe:
Powershell isn't enabled at the moment, but I know the guys are looking into using it moving forward, being able to provide a solution in Powershell might just give them the nudge they need.
I can use the COPY command in DOS but I don't know if I can tell it how to select the latest file, if you know how to do that as an operating system step I can give it a try.
October 8, 2010 at 4:04 am
John Mitchell-245523 (10/8/2010)
Have you considered using SSIS? It has built-in file manipulation tasks, so it should be just what you need.John
Unfortunately all the SSIS work is done by another team and they are "too busy", thanks for your input though.
Ideally I am looking for a powershell or activeX script that will do the job for me.
October 8, 2010 at 4:14 am
What are the file names?
October 8, 2010 at 5:21 am
Hi Graham,
If possible I don't want the solution to work by filenames, the solution should work on the date of the file. We may be looking at using a third party backup provider in the near future and I don't want to have to re-engineer a solution should the format change. Plus this way it can be spread out across multiple instances where naming standards may differ.
October 8, 2010 at 5:54 am
I have had issues with the NT modified date in the past, which is why we always put the SQL date into the filename. 3rd backup tool/provider is not going to make it any easier, most will have their own "method" of backing up and will have to use their "method" to restore.
Without xp_cmdshell, I would think that this is going to be a PowerShell script, good luck.
October 11, 2010 at 6:49 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply