MS SQL backups

[NBU35]

can any one tell me what is the diffrence between if we take backup of SQL disk from MS Window NT policy or MS SQL policy type.

and for taking backup of any client via MS SQL policy type we need to install Db extension on the client.

any thing else which we need to take care for SQL backup, and if we run SQL backup in production hours will it be effecting the SQL DB, does it also matter at this example if policy is MS Window NT policy or MS SQL policy type

[Symboy]

Hi

 If you want to backup using MS Windows NT backup , the it has to be offline backup . That means you have to stop the SQL database and back up the database files as any other flat file backup . 

When NBU Client is installed on the SQL server , it by default installs SQL Db extension .

 MS -SQL policy type allows you to backup your database even when it's up and running .Advisable not to run you SQL backup during working hours when users are connected to it . But if it's a small database , you can do that.

[wmheid]

I forget how long ago we started this process, but it has proven to 100% effective.

1.  Create a Maintenance Plan to backup the SQL DB and store it in C:\SQLBackups

2.  Exclude your Production SQL DB's from the Backup Exec Selection lists.

3.  Backup C:\SQLBackups with BE

Yes, it is a 2 step process to backup and restore, but we have had it in place for years and have never failed a single backup or restore!

[NBU35]

thanx Alot for ur help.

we are facing some issues in SQL backup, we are atking it by MS WINDOW NT policy, there are three clustered , two of the are backing up fine , but third one is failing after writing huge data with EC 25

from detailed logs:

Nov 27, 2011 2:27:00 PM - mounted GC1448; mount time: 0:00:27
Nov 27, 2011 2:27:01 PM - positioning GC1448 to file 97
Nov 27, 2011 2:28:08 PM - positioned GC1448; position time: 0:01:07
Nov 27, 2011 2:28:08 PM - begin writing
Nov 27, 2011 2:31:26 PM - Error bptm (pid=21716) cannot add fragment to image database, error = cannot connect on socket
Nov 27, 2011 2:31:36 PM - Error bpbrm (pid=19292) could not send server status message
Nov 27, 2011 2:31:37 PM - end writing; write time: 0:03:29
cannot connect on socket  (25)
 

and i am unable to find aything in bpbkar logs, please suggest.

[Marianne]

Do not even try to backup online SQL databases with MS-Windows policy.

Exclude *.mdf and *.ldf from all filesystem backups.

Either use the NBU agent to backup SQL databases or the method described by wmheid.

On the failing cluster node - can you backup anything successfully? Try a test policy and specify a small test folder. ensure that bpcd and bpbkar log folders exist on the client.

[NBU35]

i checked the policies , these policies are setup by some one else. there are two clustered nodes and one SQL server for these nodes.

both the nodes have there own policies and they are backing up there own local drives. and there are three clustered dives F, G,H. all these are being backed up in a policy which is prepared for the SQl server having two clustered nodes.

on Failing node we are able to successfully backup the local drives which are not clustered and does not contain SQL data.

please suggest

[Marianne]

If I understand correctly, the policy for the cluster data F, G and H is failing?

Can we assume that these drives contain the SQL databases and logs? If so, then MS-Windows policy is not the correct way to backup these drives while SQL is online.

As per my previous post:

Exclude *.mdf and *.ldf from all filesystem backups.

Either use the NBU agent to backup SQL databases or the method described by wmheid.

[wmheid]

I am going to "tag along" with Marianne's post and say that Windows Policy is not an option.  By combining her post and mine, the following should test the different aspects at play and at the very least tell you where to look.

1.  Copy about 100 mb's of data to each drive, F, G, and H.  I usually have an i386 folder or service pack folder somewhere close by for this.

2.  Create a backup job in NB, BE, or in this case even Windows backup and see if you can backup each of those folders using the backup i.e. that you use in your backup product.  If this step fails, then it is something in your backup config and/or security on the cluster.  If it works, do the next step.

3.  Using the appropriate SQL manager for whatever version of your SQL product, browse to your Database and do a backup manually to each of these drives.  This will test your SQL install, config, and its ability to access these drives.

4.  If you get here, check your maintenance plans and make sure they are working. 

I am assuming that all of the drives, F, G, and H are coming online?  If not, then it is a SQL Cluster security problem.

The procedure I gave earlier is what I use with Backup Exec, but in 5 years, it has never failed me.  If nothing else, use it to get your data backed up so that you can work on this problem in your spare time as opposed to having time to work on your resume because you lost the data ...

[J_H_Is_gone]

You cannot restore a SQL database that was backed up via Windows_NT policy - as the database was active and the logs were running .

the database must be stopped/quiescent to do that.

That leaves you with 2 options.

1) use the SQL agent to backup the database - this method requires you and the dba's to be involved in setting up the backup and a restore.

2) have the SQL dba schedule a sql backup via the system software and put the files in a backup dir  (don't use the c drive if you have something else avail)  The DBA will set this up, then can decide on how often and how long to keep the backup on disk.  So for example they backup it up every day at 10:00 pm to disk  E:\Backup.  I would suggest you set a policy to just get the sql servers and say start them at 2 am (this is to allow the SQL backup to disk to finish)  before you backup the server.  The DBA can keep 1 or 3 days worth of backups on disk if they want.  Then if they need to do a restore, the latest is still on disk and you don't need to be involved unless they need a copy that is no longer on disk.  Just be sure to exclude all  *.mdf and *.ldf from the backup.

[NBU35]

We are trying to configure the SQL agent on the client for backups of SQL data, also we are able to copy small folder from H drive as suggested. Thanx everyone for  ur help :)

[Mark_Solutions]

Ok - if you want a SQL Agent policy then here is what you need to do (read the guide for full details)

1. Ensure that the Master and Client can communicate - in the NetBackup Admin Console to to Host Properties - Client and double click on the SQL server to make sure it connects - your status 25 indicates that this may be an issue so troubleshoot that first - use hosts files if in doubt

2. On the first node of SQL Server open the NetBackup SQL Agent GUI, select the SQL Server name and instance and fill in the username and password - click Apply - the type of SQL (2005 etc. should all fill in if you have it right) - click OK

3. You then get a blank GUI - click file - Select for Backup

4 Select the option you want (All databases, selected, all except selected etc.) and select the radio button to save for later execution and click OK

5, Give the file a name (such as ALL_DBS.bch) and leave it in the default location

6. Repeat this on the other node so that you have set the credentials and saved the bch file to each node of the cluster

6. Create a SQL Type policy named Daily or Weekly_SQL, put the client in (virtual name), add a schedule for when it should run (leave the application one that is already in there but set the retention period on that automatic one) - put the name of the bch file in the selections section - so just type in ALL_DBS.bch)

7. Now make more policies for Weekly, Monthly and Annual if you have that sort of regime - this is required as the retention is taken from the Application Schedule so you need a policy for each retention.

8. Run it to see how it goes

9. You may also want to make one for Transacton logs but exclude the Master database from this one as you cannot back up the transaction logs for the Master database - you then need a transaction log policy for this one.

Hope this helps - sort out the Status 25 first - the client needs to be able to communicate with the Master and Media Servers for this to work.