Forums to the rescue!
I am looking for some guidance on the best way to backup my critical SQL server.
Currently I am backing it up as follows:
- Daily SMSQL full backups (every evening) - which initiate SnapMirror to secondary site (for DR).
- And 30min SMSQL log file backups which also initiate SM update to secondary site.
So every 30mins, data gets mirrored offsite. Good.
The problem with this setup is that DR is very good, but it's actually backup that I need (and in the local snapshots, due to space limitations on the primary SAN, I don't wan't to keep 30 days of snapshots for example.
So now I have thrown Protection Manager/SnapVault into the mix with a backup site SAN, but I can see that it's not possible to run a log-file only SMSQL backup and archive it to my backup SAN!?
What I would like to do is keep only 2-3 most recent snapshots locally, but then keep around 30 days archived on my backup SAN.
And continue with 30min tlog backups, which also get archived to my backup SAN.
SMSQL only gives the option to "archive" full backups though and not tlog only backups (unlike the SnapMirror option, which allows you to initiate a snapmirror relationship update after a tlog backup also). So I can run my daily full backup and archive it, and keep 30 days worth on my backup SAN, but what do I do about the 30min tlog backups? These will only be on my primary SAN, and not mirrored or backed-up!? 😕
I am guessing the only way around this is to run SnapMirror backups, and SnapVault/PM backups also? So I continue performing full daily backups which get mirrored, and continue running 30min tlog backups that get mirrored, but then in addition run a daily snapvault/PM backup of the databases (which I can then keep for my desired 30 days on my backup SAN).
Or should I simply ditch the SnapMirror backups if I don't need them, and run a full SQL backup/archive every 30mins using PM?
(I have no idea what impact this would have if any on the primary SAN).
As long as I can't lose more than 30mins of data, I am good. And as long as I have 30 days of backups, I am also good.
The DR of snapmirror is nice, but not essential.
If anyone has any suggestions or recommendations on the best way to do this I would be happy to hear them!
Thanks,
M