Video Screencast Help

Medicine for “TB”:

Created: 22 Apr 2009 • Updated: 24 Apr 2009 • 1 comment
Karthikeyan Sundaram's picture
+2 2 Votes
Login to vote

 Medicine for “TB”:

I bought a setup where 1000 and 1000 GB’s of data stored in a storage box. Users frequently access this box to store their project related data’s. We had an audit before 2 weeks and when we check for the space usage, we found 50 % of space is occupied by the SQL DB’s that are copied to this box in daily manner. I have informed DB Administrators regarding this issue and they explained me the clear setup.

Our corporation System uses MS SQL as database engine. As servers don’t have much space to store DB backups(to store one more backup copies), SQL maintenance plan is scheduled to run in mid night to backup the db’s to storage box and VERITAS backup is scheduled to run in the morning to backup the DB backup files from Storage box. DB backup files will be backed up to tapes and tapes will be sending to offshore locations in weekly manner. Suddenly I got a question -“What about the DB files that were stayed in the storage box?” and the reply was “Will clear the DB’s whenever we get free time”. I exhibited a clear smile with confused mind to them and came back.

Days went with the same setup. On a good day my mind got lights up and found two ideas; first one to use SQL Agent Backup Provided by VERITAS Netbackup and second one “Archive Backup”.

As a start I have checked SQL Agent backup. Below are features of Netbackup for SQL Server i found:

Main features of Netbackup for SQL Server:

■Full integration with the NetBackup master server and Media Manager.

■Stream-based backup and restore of SQL Server objects to tape or disk using SQL Server's high-speed virtual device interface.

■Snapshot-based backup and restore of SQL Server objects using NetBackup Snapshot Client methods

■ Backup and recovery of databases, differentials, files, filegroups, and

transaction logs.

■ Browse capability for SQL Server objects on the local and remote nodes.

■ Support for redirection of SQL Server restores to different locations.

■ Support for multiple SQL Server instances.

■ Client operation monitoring through the NetBackup Client Job Monitor.

Server monitoring is also available through the NetBackup master.

■ Performance tuning through user control of backup stripes, transfer size,

and buffer usage.

■ Job launch is supported through the following options:

■ Immediate launch through the NetBackup Database Extension GUI

■ Scheduled backup in a backup policy

■ Command line launch

■ Support for instances of SQL Server that are clustered using Microsoft

Cluster Server or Veritas Cluster Server.

■ Recovery of Microsoft SQL Server images backed up with Backup Exec,

through the Backup, Archive, and Restore (NetBackup Client) interface.

■ Support for SQL Server 2005 enhancements: page-level validation, page

level recovery, database mirroring, on-line restore, and partial database

backup and restore.

■ Partial recovery

■ Optimization for read-only filegroups

■ Verify-only restore

■ Page verification during backup and restore

■ Page-level restore

■ Backup and restore of the full text search catalog

■ Extended object information in the SQL Server catalog

■ Progress statistics during backup and restore

■ Copy-only backup

■ Compression and encryption of backups

■ Option to retry unsuccessful backups automatically

■ Ability to restore a multi-stream backup using fewer devices than it was

backed up with.

When I try to run servers SQL and file system backup in parallel, I got some errors and support suggestion was to go for different server to backup SQL databases but my management not ready to give extra resources for SQL backup.

As a Next initiative, I learned about archive and its features; Below document helped me to configure this archive process.

" This article describes how to accomplish this on a Windows machine, however, for a UNIX system, the same process would be used except to use a shell script and cron job rather than a batch file and an "at" job.

First, add a User Archive schedule to an active policy through the NetBackup Administration console. Without an User Archive schedule configured in a policy, the archive from command line will not work. Make sure to configure a proper open backup window and retention period. If the archive goes to a different set of tapes or different storage unit, use the Override settings on the Attributes tab of the User Archive schedule to change these values.

Second, create a .bat file to execute through AT or a similar scheduling product. The batch file will contain the command line:

bparchive -p policy-name -s schedule-name -L c:\data\archive_results.txt -f c:\data\filelist.txt

The -p specifies the policy name. If this is not specified, the NetBackup server uses the first policy it finds that includes the client and a user archive schedule. The -s specifies the schedule name. If it is not specified, the NetBackup server uses the first user archive schedule it finds in the policy it is using. -Lspecifies a log file. The -f specifies a list of files. Particular files can be specified in that list, including wildcards, as supported by the OS.

Example file list:

filelist.txt
-------------
c:\data\ftp
c:\data\test\*.*

With this file list, c:\data\ftp is archived to the specified storage unit, including all files and subdirectories under c:\data\ftp. Upon successful completion of the job, the c:\data\ftp directory is removed along with all files and subdirectories.

Using the wildcard of *.* the c:\data\test directory and all files and subdirectories are archived to the specified storage unit, however, upon successful completion, only the files and subdirectories of c:\data\test are deleted. The c:\data\test directory remains.

As alluded to earlier, this job must initiate from the command line and be scheduled by AT or a similar scheduling service for NT/2000. Even though the job is initiated from the command line, the archive job will show up in the Activity Log on the master server.

To restore archived files, open the Backup, Archive, and Restore GUI and use the Select for Restorepulldown to select Restore From Archived Backup...
This will then show only a list of archive backup images.

■ Backup and restore of the full text search catalog

■ Extended object information in the SQL Server catalog

■ Progress statistics during backup and restore

■ Copy-only backup

■ Compression and encryption of backups

■ Option to retry unsuccessful backups automatically

■ Ability to restore a multi-stream backup using fewer devices than it was

backed up with. "

Planned to place a Local folder in each server to store backed up DB files and once the maintenance plan completes backup, Netbackup will archive the DB’s that are under Local Folder. But I don’t have any idea to do automate backup after completion of maintenance plan. Even I don’t know about Windows scripting. And this time my DB Admins gave a hand to do.

They found an option in SQL maintenance and that allowed to me run a batch file at the end of maintenance completion. In that batch file I added my Netbackup “bparchive” command to start archiving of Local Folder.

By this Archive Process I got my Storage Space back and even server’s local drives will never shout as “Low Disk Space” J

At last I became a doctor for my storage.

Comments 1 CommentJump to latest comment

Srikanth Gubbala's picture

Thinking out of the box; awesome idea

regards, Srikanth.

-1
Login to vote