Cloning databases from a snapshot backup to a different instance on a schedule

[dopp10]

Hi All,

I'm new to this netapp community.  The company I work for recently switched our SAN system to Netapp.  We are running Windows 2008 R2 servers, SQL 2008 R2 with snapdrive 6.3 and snapmanager 5.1.

We would like the ability to clone our databases from the latest snapshot backup and restore them to a different instance on a nightly basis as read only.  I know the GUI in SMSQL allows you to do exactly what I am talking about, but there is no place that I can find that allows you to schedule the job.  Ideally those clones would automatically just point to the latest nightly snapshot that is named ..._recent every night and all I would have to worry about is that the nightly snapshots happened successfully.

Can anyone help here?

[qzhang]

You can schedule a clone operation yourself using windows task scheduler or SQL Agent job.

You can try to schedule a backup first to use as an example for how to run a schedule job using SMSQL powershell cmdlet. Use the same step as the backup job, but replace with a clone-backup powershell cmdlet. You can get cmdlet reference from IAG.

Thanks,

-Qing

[dopp10]

Isn't there just an easy way to point the clones already created to the latest snapshot every day?  I already have the clones in place where they need to be, I already have the snapshots scheduled.  I just want to point the clones to the latest snapshot everytime a new one is created.  It seems like that should be possible but I'm failing to find the configuration anywhere.

[dopp10]

Here is what is in my backup job

C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup  –svr 'SHPOLTP02\SHPOLTP02'  -ver  –verInst 'SHPOLTP02\SHPOLTP02' -mp  –mpdir 'i:\SnapMgrMountPoint' -RetainBackups  31 -lb -bksif -RetainSnapofSnapInfo 2 -trlog  -noutm  -gen –mgmt daily

The backup job works like a champ.  Now I am trying to create a clone using a different job.  Is this the proper syntax?

C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe clone-backup -svr 'shpoltp02\shpoltp02' -inst 'shpoltp02\shpoltp02' -d shpblog, reporting -tgdb shpblog_new, reporting_new -tgInst 'shpbackup01\shpprodtest' -lastBkup 0

I'm not sure what smsqljoblauncher.exe does, but it doesn't seem to work when creating a clone.

I have also tried the following command at a command prompt and it works like a charm, but when I try to put it in a sql job it fails miserably.

powershell -ImportSystemModules clone-backup -svr 'shpoltp02\shpoltp02' -inst 'shpoltp02\shpoltp02' -d shpblog, reporting -tgdb shpblog_new, reporting_new -tgInst 'shpbackup01\shpprodtest' -lastBkup 0

What am I doing wrong here?

[qzhang · Accepted solution]

OK, the current version of SmsqlJobLauncher.exe has limitation that it can only launch new-backup cmdlet. It will be updated to support all cmdlets with newer release.

For now, you can try to use

powershell.exe -PSConsoleFile "C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlShell.psc1" -Command clone-backup -svr....

to see if this works or not.

Thanks, Qing

[dopp10]

I also found the I did have the correct powershell command in my reply above, but the user that the sql agent service was using didn't have the appropriate permissions which is why it was failing.