A quick question which I hope someone can help me with.
I using SMSQL & running daily FULL backups at 00:00 every day and 18x hourly tlog only backups.
I want to keep backups going back 4 days.
Therefore I have 2 separate SMSQL jobs scheduled: 1 for the FULL backups (which include Tlogs also) and 1 for the hourly tlog only backups.
The SQL job params look like this:
TLogs params: -RetainBackupDays 4 -lgbkonly -bksif -RetainSnapofSnapInfoDays 4 -trlog -noutm –mgmt standard
Full backup params: -RetainBackupDays 4 -lb -bksif -RetainSnapofSnapInfoDays 4 -trlog -noutm –mgmt daily
- By default a backup also forces a "snapshot of the snapinfo LUN" - this means that after every backup (whether it be a tlog or full) the vol holding the snapinfo LUN is snapshot! Is there a better way to configure this? Maybe only do volume level snapinfo snapshots once per day i.e. when my full backup runs & not after every tlog backup?
My problem is that the SnapIno LUN always has plenty of space and can hold 4 days of SQL backups no problems - but the volume snapshots didn't seem to get deleted when I was using the default "-RetainSnapofSnapInfo 8" command (it wasn't keeping only the last 8 - it was keeping more than 10days worth which eventually filled my volume). When this happens SMSQL doesn't generate an error as the backup worked - only the snapshot of the vol failed. Instead I get a Filer volume warning telling me my volume is 100% full.
The only option in SMSQL is to either turn "create snapshot of snapinfo drive after backup" on or off - which means after every hourly transaction log backup it also snapshots the snapinfo drive. Is this not overkill? I have 4 days of streamed backups on the LUN, then can I simply keep 1 day of snapinfo snapshots?? Why are the snapinfo snaps so important if we have the backups available on the LUN anyway?
I've read a great post about FR and SMSQL, but I can't find much regarding this "-RetainSnapofSnapInfo" and how it should be used when performing regular SMSQL backups.
Thats exactly how I want my configuration - and I only have about 10Gb combined of databases and logs - and my databases and logs are also on different LUNs & volumes: 1 LUN for databases, 1 for logs, 1 for system dbs and 1 for snapinfo.
Can I ask a few questions:
- What version of SMSQL are you using? (I am using SQL2005 and SMSQL v5).
- How long are you keeping your backups for?
- When you perform the tlog only backup every 15mins, does it not also make a snapshot of your snapinfo LUN?
- if not, then how often do you make a snap of your snapinfo LUN, and how long do you keep them for?
(when I look at my filer, I can see hourly snapshots of my snapinfo vol because its snapped after every tlog backup - this is why I can't lower the tlog backup frequency to 15mins - I would simply eat up even more space on my filer).
- In SnapManager, under "Backup Settings|Transaction log backup" theres an option "Create Snapshot of snapinfo drive after after backup" - this is obviously my problem. I was told not to disable this though as it could affect my ability to restore!? How have you configured this option?
- Would it be possible for you to post your SQL job command strings here so I can compare them to mine - for both your tlog & full backup jobs?
Sorry for all the questions but I really appreciate the help nailing this. All the documentation but very few real-life examples on how to configure backups etc. We've been using SMSQL for a long time now, but it was originally configured by my NetApp consultants to only run a full backup at 00:00 every day - now I have a business critical database running on it I needed to reduce potential data loss by running more frequent backups - which is when I hit all these issues. The easy answer is to oversize my volume to keep all these snapshots - but for 10Gb of data I am already taking up more than 160Gb of space on my filer for the various LUNs/vols mentioned above.
1). SQL2005, SMSQL5 & Snapdrive 6.02 - We also have SQL2000, SMSQL 2.1
and snapdrive 4.2.1
2). Between 2 and 4 weeks. We also have 2 years of snapvaults
3). Yes it does go into the snapinfo and take filer storage
We use the SQL Agent to pass the command
C:\Program Files\NetApp\SnapManager for SQL Server\SmSqlBi.exe -H
"DMSQV01" -D 30 -L -J -K 30 -S "DMSQV01" -C 0 -N -UN -UM
every 15 minutes
Hope it helps
Maybe I am being stupid here, but I don't understand this. On my SQL2005 server agent, SMSQL has passed the following string to our SQL agent:
tlog job: C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup –svr 'BNK-DB02' -d 'BNK-DB02', '10', 'BAX40Production', 'BAX40test', 'BAX40Validation', 'INVOICES55', 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'Servicedesk' -ver –verInst 'BNK-DB02' -mp –mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint' -RetainBackupDays 4 -lgbkonly -bksif -RetainSnapofSnapInfoDays 2 -trlog -noutm –mgmt standard
full job: C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup –svr 'BNK-DB02' -d 'BNK-DB02', '10', 'BAX40Production', 'BAX40test', 'BAX40Validation', 'INVOICES55', 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'Servicedesk' -ver –verInst 'BNK-DB02' -mp –mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint' -RetainBackupDays 4 -lb -bksif -RetainSnapofSnapInfoDays 2 -trlog -noutm –mgmt daily
Its called "SmsqlJobLauncher.exe" in my jobs. Whats this "SmSqlBi.exe" used in yours? (different version of SQL server perhaps?).
From the above you can see that I keep 4 days of backups, and 2 days of Snapinfo snaps during this "testing period" that I am going through.
I am aiming for daily full backups, with at least hourly tlog backups throughout the day - but at the moment we are simply eating up too much space on our Filer to do this (4.5Gb of logs = 70Gb vol). And I only need to keep 4 days of backups and we have fairly small data change - so I have something misconfigured somewhere.
C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe new-backup –svr 'S1SQV01' -d 'S1SQV01', '19', 'master', 'model', 'msdb', 'SharedServices_DB', 'SharedServices_KC_DB', 'SharedServices_KC_Search_DB', 'SharedServices_Search_DB', 'SharePoint_AdminContent', 'SharePoint_AdminContent_5', 'SharePoint_Config', 'SharePoint_KC_Config', 'WSS_Content', 'WSS_Content_Fusion', 'WSS_Content_mySites', 'WSS_KC_Content_KMCentre', 'WSS_KC_Content_mySites', 'WSS_KC_Content_SSP1', 'WSS_KC_Search_SPKC01', 'WSS_Search_SPMOS01' -ver –verInst 'S1SQN01' -mp –mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint' -RetainBackups 7 -lb -bksif -RetainSnapofSnapInfo 0 -trlog -updmir -verdest –mgmt daily
From a Windows 2003 server running SQL 2005, snapdrive 6.02 and smsql 5.
Is this your tlog backup, or your full-backup?
I am a little confused as you said that you were doing 15mins tlog backups (including snapshots of snapinfo), and 2x daily full backups - but these params look like you're not doing any snaps of your snapinfo LUN when you perform the backup!?!?
-RetainBackups 7 -lb -bksif -RetainSnapofSnapInfo 0 -trlog -updmir -verdest –mgmt daily
Or am I misunderstanding something here?
Sorry I only have the scripts for TL on the older SMSQL versions. I guess this is a new issue I will also have to resolve, as we are going to upgrade some more servers next month.
Good luck and please post back when you have the answer. As I will need it. 😉