cancel
Showing results for 
Search instead for 
Did you mean: 

How to backup multiple instance of SQL backup on single host

BVshet
Level 5

Hi Team,

I am trying to backup 2 instances of SQL hosted on one server using the below script

SQLINSTANCE MSSQLSERVER_0
OPERATION BACKUP
DATABASE $ALL
SQLHOST "XYZ"
NBSERVER "NBU77"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
POLICY BKPSQL
NUMBUFS 2
RETRYTYPE AUTO
RETRYWAITSECONDS 60
NUMRETRIES 1
ENDOPER TRUE

SQLINSTANCE MSSQLSERVER_1
OPERATION BACKUP
DATABASE $ALL
SQLHOST "XYZ"
NBSERVER "NBU77"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
POLICY BKPSQL
NUMBUFS 2
ENDOPER TRUE

 

My backup are failing with error 

 

Please help.

 

USER - A batch file that uses <DATABASE $ALL>, <FILEGROUPS $ALL>, or <FILES $ALL> must specify exactly one operation.

 

3 REPLIES 3

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

Have you tried this?

OPERATION BACKUP
SQLINSTANCE $ALL

DATABASE $ALL
SQLHOST "XYZ"
NBSERVER "NBU77"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
POLICY BKPSQL
NUMBUFS 2
RETRYTYPE AUTO
RETRYWAITSECONDS 60
NUMRETRIES 1
ENDOPER TRUE

Hi Marianne,

Yes, I did try but the problem is we are not authorised to start SQL browser services and without that service the NBU only considers the default instance and will only backup default instance data.

So I am trying to mentione two instances in single script and get it working.

However it works if I mention indivisual DB and instance names.

Michal_Mikulik1
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello,

put each instance in its own script. Both scripts can be then in one policy.

Regards

Michal