cancel
Showing results for 
Search instead for 
Did you mean: 

Configuring MS-SQL backup

bst740
Level 4
Partner Accredited Certified

Hello everyone!

 

I have 2 question on configuring MS-SQL Backup

 

1) I created 2 scripts in client: first for full backup of my databases, second for transaction logs backup for the sames databases. Can I Configure it in one policy? I need to execute full backup once at week, and transaction log once at hour. How do I need to configure policy and schedules? Do I need to create a new policy for transaction logs?

 

2) My backup executes correctly, when it's initiated by master server during the backup window. But I can't execute manual backup, it fails. How to configure it correctly?

 

Thanks!

 

P.S. Master, media, client are 7.5.0.6 running on Win 2008 R2, MS SQL 2008

1 ACCEPTED SOLUTION

Accepted Solutions

Mark_Solutions
Level 6
Partner Accredited Certified

You need at least two policies.

First for the full backup - note that it actually runs using the Default Application backup schedule so this needs a full single window and to have its retention period set to what you want it to be

Second for the transaction logs - again with the Default Application Backup schedule having the retention you need

If you do Monthly and Annual backups you will also need additional policies for those as you can only have one default application backup schedule in a policy and the jobs retentention period is taken from that schedule

Hope this helps

View solution in original post

26 REPLIES 26

Mark_Solutions
Level 6
Partner Accredited Certified

You need at least two policies.

First for the full backup - note that it actually runs using the Default Application backup schedule so this needs a full single window and to have its retention period set to what you want it to be

Second for the transaction logs - again with the Default Application Backup schedule having the retention you need

If you do Monthly and Annual backups you will also need additional policies for those as you can only have one default application backup schedule in a policy and the jobs retentention period is taken from that schedule

Hope this helps

bst740
Level 4
Partner Accredited Certified

Ok, I need to back up logs every hour, and default setting is 1 week. I can't configure frequency in default-application backup schedule.

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

The Automatic backup schedule is what will kick off the hourly backup.

The Default Application Backup schedule is what is backing up the actual client-initiated streams and determines the retention period.

Please go through the NBU for SQL manual: http://www.symantec.com/docs/DOC5173

It contains detailed steps as well as explanation of the different schedule types.

Mark_Solutions
Level 6
Partner Accredited Certified

As Marianne says .. it is "Your" schedule that decides when it runs - the only thing you need to change on the Default one is your retention period

Yours will set to every 1 hour without a problem

To clarify (as as pointed out it is worth going through the manual) ...

When you create a policy it has a Default Application Schedule - this is like a user backup window so leave this as it is apart from setting the retention period

You then create your Automatic schedule - this decides when the job will actually run. When it does run it calls the bch script on the client which then does a "User" backup, one at a time, for each database. When this runs it used the Default application schedule which is why this is where the retention is decided and why it needs a full backup window (as it has when you create the policy)

Hope this clarifies things

AjinBabu
Level 3

HI,

1.        You may need to create two different policies one for FULL back up with a scheduler of weekly and one for transaction log with the scheduling to run hourly.

2.       You mean to say Manual backup from NBU console Or from the NBU database extension GUI?

Regards

Ajin

bst740
Level 4
Partner Accredited Certified
2 - from the nbu console. Now I Scheduled logs backup with second policy and I've error 41 and 13 41: 29.08.2013 15:46:58 - Info nbjm(pid=2672) starting backup job (jobid=312) for client CDC01-DISQL--01.DC-DEVELOP.TN.CORP, policy DI_SQL_logs, schedule Default-Application-Backup 29.08.2013 15:46:58 - Info nbjm(pid=2672) requesting STANDARD_RESOURCE resources from RB for backup job (jobid=312, request id:{BF465991-4AE8-4105-9493-46D6660AA730}) 29.08.2013 15:46:58 - requesting resource Dedupe_SUG 29.08.2013 15:46:58 - requesting resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_CLIENT.MAXJOBS.CDC01-DISQL--01.DC-DEVELOP.TN.CORP 29.08.2013 15:46:58 - requesting resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_POLICY.MAXJOBS.DI_SQL_logs 29.08.2013 15:46:58 - granted resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_CLIENT.MAXJOBS.CDC01-DISQL--01.DC-DEVELOP.TN.CORP 29.08.2013 15:46:58 - granted resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_POLICY.MAXJOBS.DI_SQL_logs 29.08.2013 15:46:58 - granted resource MediaID=@aaaaj;DiskVolume=PureDiskVolume;DiskPool=Dedupe_Pool_02;Path=PureDiskVolume;StorageServer=sdc01-dintbak02.dc-develop.tn.corp;MediaServer=sdc01-dintbak02.dc-develop.tn.corp 29.08.2013 15:46:58 - granted resource SU_Dedup_02 29.08.2013 15:46:58 - estimated 0 Kbytes needed 29.08.2013 15:46:58 - Info nbjm(pid=2672) started backup (backupid=CDC01-DISQL--01.DC-DEVELOP.TN.CORP_1377776818) job for client CDC01-DISQL--01.DC-DEVELOP.TN.CORP, policy DI_SQL_logs, schedule Default-Application-Backup on storage unit SU_Dedup_02 29.08.2013 15:47:00 - started process bpbrm (7688) 29.08.2013 15:47:02 - Info bpbrm(pid=7688) CDC01-DISQL--01.DC-DEVELOP.TN.CORP is the host to backup data from 29.08.2013 15:47:02 - Info bpbrm(pid=7688) reading file list from client 29.08.2013 15:47:02 - connecting 29.08.2013 15:47:04 - Info bpbrm(pid=7688) listening for client connection 29.08.2013 15:47:10 - Info bpbrm(pid=7688) INF - Client read timeout = 300 29.08.2013 15:47:11 - Info bpbrm(pid=7688) accepted connection from client 29.08.2013 15:47:11 - Info bphdb(pid=156) Backup started 29.08.2013 15:47:11 - Info bptm(pid=7736) start 29.08.2013 15:47:11 - Info bptm(pid=7736) using 262144 data buffer size 29.08.2013 15:47:11 - connected; connect time: 00:00:09 29.08.2013 15:47:13 - Info bptm(pid=7736) setting receive network buffer to 1049600 bytes 29.08.2013 15:47:13 - Info bptm(pid=7736) using 30 data buffers 29.08.2013 15:47:15 - Info bptm(pid=7736) start backup 29.08.2013 15:47:17 - Info bptm(pid=7736) backup child process is pid 7800.1648 29.08.2013 15:47:17 - Info bptm(pid=7800) start 29.08.2013 15:47:17 - begin writing 29.08.2013 15:47:18 - Info bphdb(pid=156) dbclient(pid=156) wrote first buffer(size=65536) 29.08.2013 15:52:26 - Info sdc01-dintbak02.dc-develop.tn.corp(pid=7736) StorageServer=PureDisk:sdc01-dintbak02.dc-develop.tn.corp; Report=PDDO Stats for (sdc01-dintbak02.dc-develop.tn.corp): scanned: 2 KB, CR sent: 0 KB, CR sent over FC: 0 KB, dedup: 100.0% 29.08.2013 15:52:27 - Info bphdb(pid=156) done. status: 41: network connection timed out 29.08.2013 15:52:27 - end writing; write time: 00:05:10 network connection timed out(41) 13: 29.08.2013 15:46:58 - Info nbjm(pid=2672) starting backup job (jobid=314) for client CDC01-DISQL--01.DC-DEVELOP.TN.CORP, policy DI_SQL_logs, schedule Default-Application-Backup 29.08.2013 15:46:58 - Info nbjm(pid=2672) requesting STANDARD_RESOURCE resources from RB for backup job (jobid=314, request id:{2812A647-4E88-4258-B16E-FEB62BF9A491}) 29.08.2013 15:46:58 - requesting resource Dedupe_SUG 29.08.2013 15:46:58 - requesting resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_CLIENT.MAXJOBS.CDC01-DISQL--01.DC-DEVELOP.TN.CORP 29.08.2013 15:46:58 - requesting resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_POLICY.MAXJOBS.DI_SQL_logs 29.08.2013 15:46:58 - Info nbrb(pid=1704) Limit has been reached for the logical resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_CLIENT.MAXJOBS.CDC01-DISQL--01.DC-DEVELOP.TN.CORP 29.08.2013 15:48:58 - Info nbjm(pid=2672) Waiting in NetBackup scheduler work queue on server CDC01-PINTBAK01.DC-PROD.TN.CORP 29.08.2013 15:50:58 - Info nbjm(pid=2672) Waiting in NetBackup scheduler work queue on server CDC01-PINTBAK01.DC-PROD.TN.CORP 29.08.2013 15:52:58 - Info nbjm(pid=2672) Waiting in NetBackup scheduler work queue on server CDC01-PINTBAK01.DC-PROD.TN.CORP 29.08.2013 15:54:58 - Info nbjm(pid=2672) Waiting in NetBackup scheduler work queue on server CDC01-PINTBAK01.DC-PROD.TN.CORP 29.08.2013 15:56:58 - Info nbjm(pid=2672) Waiting in NetBackup scheduler work queue on server CDC01-PINTBAK01.DC-PROD.TN.CORP 29.08.2013 15:57:57 - granted resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_CLIENT.MAXJOBS.CDC01-DISQL--01.DC-DEVELOP.TN.CORP 29.08.2013 15:57:57 - granted resource CDC01-PINTBAK01.DC-PROD.TN.CORP.NBU_POLICY.MAXJOBS.DI_SQL_logs 29.08.2013 15:57:57 - granted resource MediaID=@aaaaj;DiskVolume=PureDiskVolume;DiskPool=Dedupe_Pool_02;Path=PureDiskVolume;StorageServer=sdc01-dintbak02.dc-develop.tn.corp;MediaServer=sdc01-dintbak02.dc-develop.tn.corp 29.08.2013 15:57:57 - granted resource SU_Dedup_02 29.08.2013 15:57:57 - estimated 0 Kbytes needed 29.08.2013 15:57:57 - Info nbjm(pid=2672) started backup (backupid=CDC01-DISQL--01.DC-DEVELOP.TN.CORP_1377777477) job for client CDC01-DISQL--01.DC-DEVELOP.TN.CORP, policy DI_SQL_logs, schedule Default-Application-Backup on storage unit SU_Dedup_02 29.08.2013 15:57:59 - started process bpbrm (6528) 29.08.2013 15:58:01 - Info bpbrm(pid=6528) CDC01-DISQL--01.DC-DEVELOP.TN.CORP is the host to backup data from 29.08.2013 15:58:01 - Info bpbrm(pid=6528) reading file list from client 29.08.2013 15:58:01 - connecting 29.08.2013 15:58:03 - Info bpbrm(pid=6528) listening for client connection 29.08.2013 15:58:09 - Info bpbrm(pid=6528) INF - Client read timeout = 300 29.08.2013 15:58:10 - Info bpbrm(pid=6528) accepted connection from client 29.08.2013 15:58:10 - connected; connect time: 00:00:09 29.08.2013 15:58:11 - Info bphdb(pid=156) Backup started 29.08.2013 15:58:11 - Info bptm(pid=7620) start 29.08.2013 15:58:11 - Info bptm(pid=7620) using 262144 data buffer size 29.08.2013 15:58:13 - Info bptm(pid=7620) setting receive network buffer to 1049600 bytes 29.08.2013 15:58:13 - Info bptm(pid=7620) using 30 data buffers 29.08.2013 15:58:15 - Info bptm(pid=7620) start backup 29.08.2013 15:58:17 - Info bptm(pid=7620) backup child process is pid 8156.8024 29.08.2013 15:58:17 - Info bptm(pid=8156) start 29.08.2013 15:58:17 - begin writing 29.08.2013 15:58:19 - Info bphdb(pid=156) dbclient(pid=156) wrote first buffer(size=65536) 29.08.2013 15:59:00 - Error bpbrm(pid=6528) socket read failed, An existing connection was forcibly closed by the remote host. (10054) 29.08.2013 15:59:00 - Error bptm(pid=8156) socket operation failed - 10054 (at child.c.1296) 29.08.2013 15:59:00 - Error bptm(pid=8156) unable to perform read from client socket, connection may have been broken 29.08.2013 15:59:08 - Info sdc01-dintbak02.dc-develop.tn.corp(pid=7620) StorageServer=PureDisk:sdc01-dintbak02.dc-develop.tn.corp; Report=PDDO Stats for (sdc01-dintbak02.dc-develop.tn.corp): scanned: 2 KB, CR sent: 0 KB, CR sent over FC: 0 KB, dedup: 100.0% 29.08.2013 15:59:08 - Info bphdb(pid=156) done. status: 13: file read failed 29.08.2013 15:59:08 - end writing; write time: 00:00:51 file read failed(13)

Mark_Solutions
Level 6
Partner Accredited Certified

During a SQL backup the client talks directly to the Master Server even though it passes its data to the Media Server

Ensure that the SQL client is resolving the Master Server by the correct name and IP address (FQDN and short host name) and vice - versa, use Hosts files in needs be

bst740
Level 4
Partner Accredited Certified

and after few minutes I have succesfully executed job (in attach)

 

Mark_Solutions
Level 6
Partner Accredited Certified

That did finish with a Status of 1 so check the logs - if you selected All Databases in your logs bch file then re-create it to exclude the Master database as you cannot do logs for that one - other wise the logs on the client will tell you which database could not have its logs backed up (probably in the wrong mode but the ogs do tell you)

When happy dont forget to close the thread by marking the advice that helped the most using the "Mark as solution" option - this helps other searching for similar details in the future

bst740
Level 4
Partner Accredited Certified

I'm Sorry, what logs in client I need to check?

 

In my bch file I selected some needed DBs - not all. All selected DBs are in FULL mode, not simple.

Mark_Solutions
Level 6
Partner Accredited Certified

If a job fails it will get logged on the client under program files\veritas\netbackup\logs\mssql_backup_failures

If it is partly successful it will log under program files\veritas\netbackup\logs\user_ops\mssql\ or similar - you will see the structure it uses

If you want it all stored in one place the create program files\veritas\netbackup\logs\dbclient\ and it will there

bst740
Level 4
Partner Accredited Certified

Mark, log from \dbclient\ is in attach. Errors are same.

 

In addition I suspect that there is a confusion in FQDN and DNS names in settings.

 

I tried to indicte FQDN everywhere: in policy -> clients, on BAR on client, on Netbackup MS SQL Client

But in logs sometime a see a DNS name....

 

THanks!

Mark_Solutions
Level 6
Partner Accredited Certified

Lots of issues in that log by the looks of it

Have you set the NetBackup Client Service and NetBackup Legacy Network Service on the client to use an account that has sysadmin rights to all databases? - This is needed for the version and O/S of your SQL Server

Try that first, restart the services on the client to register the logon accounts and try the backup again and then send the new logs if it doesn't work

bst740
Level 4
Partner Accredited Certified

I changed accounts for this services, errors are same (in attach).

Thanks

Mark_Solutions
Level 6
Partner Accredited Certified

Another couple of questions then ...

1. Is that account also a local admin on the SQL Server

2. Are you running this from a policy or from the client

Could you also post your bch files for checking (which i assume you created on the client itself using the NBU SQL GUI?)

bst740
Level 4
Partner Accredited Certified

1) For testing I tried an account of domain admin

2) I run it from policy in master

 

Two scripts for logs and full backup are in attach

 

Thanks!

 

 

Mark_Solutions
Level 6
Partner Accredited Certified

Domain admin is not likely to be enough - it must be an account that has sysadmin rights to all SQL Databases - your SQL Admin needs to provide that or give your Domain Admin account such rights within SQL

I asked about that account having local admin rights as i saw this line in the log file:

<16> readCommFile: ERR - timed out after 900 seconds while reading from C:\Program Files\Veritas\NetBackup\Logs\user_ops\mssql\logs\0902113000057-4012-3856-000-000-prg

However, you have also set a grouping of 5 on the job (was there a reason for that?) so it may be taking longer to get the file list togeter and you may need to increase the client read timeout settings on the Master and Media Servers to get this to work

I would try tesing it with a simple basic bch file first - just one database and no additions specification in it which could hold things up:

OPERATION BACKUP
DATABASE "CitrixXenDesktopDB"
SQLHOST "CDC01-DISQL--01.DC-DEVELOP.TN.CORP"
NBSERVER "CDC01-PINTBAK01.DC-PROD.TN.CORP"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
BROWSECLIENT "CDC01-DISQL--01.DC-DEVELOP.TN.CORP"
NUMBUFS 2
ENDOPER TRUE

bst740
Level 4
Partner Accredited Certified

My domain administrator have sysadmin rights in SQL. Additionally I gave him rights on all my databases.

 

I configured policy with your script and second for logs. I will try it this night. Tomorrow i'll notify you.

 

Additionally I forgot to mention that SQL is in MS fileover cluster services. Agent is installed on both nodes. In policy and client settings there is a FQDN of clustered service.

 

Thanks you for your help!

 

 

bst740
Level 4
Partner Accredited Certified

Hi!

 

Some backups with test script finished successfully (at 8-34), and the seconf (at 9-34) fails with timeout

Logs in attach