November 7, 2014 at 4:44 am
Hi all,
I need to create a dynamic backup and restore script that will connect to one instance of sql server, backup the databases (not all) but as per need, restore the database on a different instance, and then fix the orphaned user if any. The problem is that I need to generate the backup file on the fly on demand, so there is no point that I can copy and restore it to different instance. Now, I tried doing this in sqlcmd mode from ssms I was able to do both backup and restore (script attached). However, I would like to have this created as a sql job like a one sql job that dynamically handles this, and have it run on demand, but as I understand sqlcmd mode in ssms can't be used in sql job and we have to use sqlcmd in the context of operation system (cmdexec) type mode.
I've scratched my head around this, did google search but didn't got any success on this. I hope if someone can help me out on this.
Thanks!
Regards,
Faisal
November 10, 2014 at 9:47 am
How familiar are you with powershell or even command line programing as this can be easily achieved by creating either a powershell script and utilising the sql server modules or via a batch file using sqlcmd.
It would require your SQL server agent to be setup as an account that has the necessary privilages to run the required backup \ restore against the SQL servers and access the files that are written to and read from.
You would then have a SQL agent job that called the .bat or .ps1 script and this can be executed on demand or at scheduled times.
MCITP SQL 2005, MCSA SQL 2012
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply