How do you setup MSSQL Backups?

This Wiki is brought to you by Backup Central, where you can find the Mr. Backup Blog, Forums, and a mailing list for each forum!
Backup FAQs	Service Providers	Backup Software	Backup Hardware	Backup Book Wiki	Free Stuff	Miscellaneous

A. Joseph Ortiz posted (31 March 2000): To cover the basics in order since this question seems to get repeated frequently here:

CLIENT SIDE If you are using version 1.x of the BSM, you set the appropriate variables in the nsrsql.bat file. It contains variables for the nsr server, pool, group, etc. but they are all normally commented out. You have to adjust them to your environment and uncomment them.

If you are using version 2.x of the BSM, then everything on the client side gets set up via the BSM SQL User GUI. There is NO client nsrsql.bat file. If you upgraded from 1.x to 2.x BSM, be sure you DELETE the old nsrsql.bat file after you get your environmental settings out of it. If you're backing up SQL 7.0, then you must use the 2.x version of BSM for SQL.

If you want all SQL data going to the SQLData pool (I'm assuming you also want the transaction logs, if you're also doing Incrementals on the SQL databases, going to the same pool), for the 1.x version BSM, you would set both the NSR_DATA_VOLUME_POOL and NSR_LOG_VOLUME_POOL variable to SQLData. If you're using the 2.x version of BSM, then you would set both the BSM_SQL_DATA and BSM_SQL_LOGS variables to SQLData which matches your pool definition on the server. If you want Incrementals, transaction logs separate, then you would need a pool set for those as well and the NSR_LOG_VOLUME_POOL or BSM_SQL_LOGS variablewould be set to the name of that pool for the SQL Incrementals.

With SQL 7.0, the client setup gets a bit more involved because you also have to get a specific ID set up in the SQL database with the appropriate settings and permissions and password. This info. is then entered via the BSM 2.x SQL User GUI.

SERVER SIDE On the server side, set up the necessary schedule(s) to provide for the Full, Incr., etc. backup levels on the appropriate days. I'm assuming you have 2 client definitions for your SQL client. One definition, which is the client's "base" definition with a save set of ALL, and which is probably a member of the Default group. Then you have a 2nd definition which belongs ONLY to the SQL group (NTSQL in your case) and has a save set spec of MSSQL:.

If you're using the 1.x version of the BSM, then you reference the nsrsql.bat in the Backup Command field of the client definition. If you're using the 2.x version of the BSM, then you reference the nsrsqlsv command, with any appropriate parameters, in the Backup Command field of the client definition.

Do NOT specify any directives for the MSSQL client definition.

POOL SIDE

Remember that pools are simply "glorified" data filters that sort data based on 4 criterion in descending order of precedence: 1) Group Membership 2) Client Name 3) Save Set spec 4) Backup Level(s) The pool you set up for the SQLData would have a matching tape label (unless you're using barcodes with matching turned on) and the backup level would be set to FULL. You would also have ONLY the SQL Group selected (NTSQL in your case). If you're also going to direct Incrementals/transaction logs for SQL to this same pool, you would additionally select backup levels 1 and INCR. You would also have the SaveIndex option turned on.

Somewhere along the line you may have missed a setting. Data usually goes to the Default pool when it fails to meet the criteria of any other pool definition.