Data Backup and Recovery

Another SMSQL question regarding "retainsnapofsnapinfo".

marcconeley
5,382 Views

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

Now:

- 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.

Thanks,


Marc

11 REPLIES 11

BrendonHiggins
5,361 Views

Hi

I have my data and TLs on different LUNs.  I do a full backup 3 times a day but also snapmirror the TLs every 15 minutes.  This does not have the snapinfo issue.

Bren

marcconeley
5,361 Views

Hi Bren,

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.

Cheers,

Marc

BrendonHiggins
5,361 Views

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

Bren

marcconeley
5,361 Views

Hi Bren,

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.

BrendonHiggins
5,361 Views

Sorry

posted script from SQL2000 and SMSQL2.1

Will post correct version tomorrow.

Bren

marcconeley
5,361 Views

Any further news on this Bren?

BrendonHiggins
5,361 Views

Sorry forgot all about it.  Wait one.

BrendonHiggins
5,361 Views

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.

marcconeley
5,361 Views

Hi Bren,

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?

Marc

BrendonHiggins
4,412 Views

Full

Sorry did not re-read the thread.  Wait one

BrendonHiggins
4,411 Views

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.  😉

Bren

Public