Using SMSQL and SQL Native Backup

[elarin010]

All,

We currently use SMSQL for our normal full backups and transaction log backups. I am planning on using the native SQL backup engine to create full database backups that will be used for cloning to another instance only. Yes, I realize SMSQL has a clone option but it doesn't satisfy our needs when cloning to another SQL instance. Particularly because it always uses mount points on restore instead of LUN's local to the other instance and that it can't be scheduled or scripted out.

SMSQL will still be the primary backup utility. What I'd like to know is are there any repercussions in using both SMSQL and SQL like this. Thanks!

Erick

[rzeleznik]

Hi Erick,

You have a few different options.  You could use the regular sql backup with the copy_only option:

BACKUP DATABASE database_name TO <backup_device> WITH  COPY_ONLY

Netapp's official stance will probably be that you shouldn't interfere with the backup sequencing of SMSQL.  This makes sense as it is a general guideline to only have 1 dedicated backup process for SQL Server.  I have seen quite a few instances where people didn't understand how the backup and transaction log cycles worked and would do other back ups to refresh dev/staging and not realizing they were breaking the restore chain.  Clearly enough people needed to do this sort of thing and hence MS finally introduced the copy_only clause.

There is a catch though, that you want to be sure you schedule these out of cycle backups so as to not overlap with SMSQL. You could just disable the transaction log backup jobs in sqlagent until you are done with the native backup(s).

So that is first direct answer to your question, yes it can be done the way you are thinking.  However, why don't you leverage the fact that complete copies available in the snapshots that can be mounted and copied anywhere?  This will mitigate any sort of complications with someone forgetting the copy_only clause, schedule alignment, not to mention server load.

You could mount the snapshots made by SMSQL as luns on the destination server with snapdrive.  Once mounted as driver letters or mount points, you could just attach the mdf/ldf files and use the dbs for a short period.  This is really only an option if you are trying to recover some data or need to take a peek at an older version of the db.  The problem is that the snapshot will be locked unless of couse you snap clone which eventually will cause an issue for SMSQL.  Probably the better path is once the snapshot is mounted, copy the mdf/ldf for the databases to a location in your dev/stage environment.

Let me know if you want any more details.

Rolland

[elarin010]

Thank you for the info. We already take advantage of the "Clone Wizard" in SMSQL to temporarily restore point-in-time snapshots to another instance when we need to recover some data or take a peek like you mentioned.

However, we have a permanent test SQL instance in which we'd like to automatically clone some prod db's to it on some type of schedule with no user intervention required. This is why I'm leaning toward the native SQL operations for backup/restore. I didn't know about the COPY_ONLY option which doesn't affect the normal sequence of backups like you stated. It sounds like that may be a good fit for what I'm looking for.

Erick

[qzhang]

Particularly because it always uses mount points on restore instead of LUN's local to the other instance

I am not sure why this is a problem. The cloned database can point to the same mount point path every time you do the clone. In any case, you can use available drive letters if you want, so the clone database can always mount to drive Z:\, for example, if you need to have same drive every time you do the clone. Just remember to delete the old clone before creating new one.

and that it can't be scheduled or scripted out.

You can easily use clone-backup or clone-database along with delete-clone PowerShell cmdlet to schedule the clone or write whatever script you need.

Thanks,

-Qing

[rzeleznik]

Why don't you want to use the snapshot copies of the mdf/ldfs?  Either way you are writing scrips and copying files etc.  With the snapshot of the native files, you just have to copy them and attach.  With the backup approach, you have to backup, copy and restore (way more IO and duration).  You can mount the snapshots as luns in your dev environment and handle the copy process all off of production.  Perhaps its just my thought to minimize access production when there are alternatives.

[elarin010]

How do you specify drive letter(s) to mount when using the "clone-backup" cmdlet?

Erick

[elarin010]

Hey all. I decided to go with using the latest full backup snapshots instead. I wrote a few powershell scripts to do this. The first uses the SMSQL cmdlets to query the backup set and connect the newest one via SnapDrive cli commands. Then I use SQL powershell to detach the existing databases, copy the .mdf/.ldf files over and attach them. Seems to work pretty good.

Erick

[rzeleznik]

Great to hear that worked out well for you!

[1ptlatham1]

Erik, Do you have a generic version of this script that you could post?

Thanks,

Todd

[cdoppmann]

I'm looking to do the same thing.  A generic script would help me greatly.

[elarin010]

All,

Attached is a generic version of a powershell script I wrote to clone database(s) from one instance to another using SMSQL backup sets. I'm sure there's room for improvement and hope it helps you get started.

Erick

[1ptlatham1]

Erick,

Thanks for the script. I will look into it. I appreciate the help.

Sincerely,

O

PT