Backing Up Multiple Instances on SQL

[dms19]

We have approx 5 instances of SQL running on a single server....we can successfully back up a single instance with the .opt, .cmd and .cfg file pointed to the proper instance but how do we go about doing all of them via a schedule?

How would we incorporate multiple .cfg files?

Thanks!!

 

[moon-buddy]

Just replace the DB name with a * to backup all of the db instances.

 

[dms19]

That will back up all of the databases under a single instance, but I need to back up instance#1, instance#2, instance#3, etc... with all of the databases under each instance.

In the end I made seperate .opt files, seperate .cfg files and seperate .cmd files as well as gui shortcuts all giving the specific related .opt, .cfg etc.

 

[moon-buddy]

Why do you have to do separate instances? Any particular reason why this must be separated?

 

[dms19]

I have no idea what the business reason is for a single server running seperate instances with mulitple databases, when they could all be under on one instance, or the instances could run on seperate VMs.

But until figuring out the seperate .opt's and .cfg's the command file would only grab the databases under whatever instance was present in the .cfg.

 

[GregE]

TDPSQLC has an option for the .cfg file and .optfile:
/CONFIGfile=
/TSMOPTFile=

Maybe that's what you already found.

 

[dms19]

Yup, thanks Greg! It took some looking but I found that solution.

Thanks again

 

[rore]

Hi,
No need to have multiple cfg nor opt files (unless you really need different specific settings like performance, different TSM servers, etc).

Just add in your TDP backup job (cmd or bat) a line per instance like:
tdpsqlc backup mydatabase1,mydatabase2,.... [full/diff....] /SQLSERVER=myserver\instance .....

Rudy

 

[dms19]

Interesting...thanks Rudy - I'll play around with that too.

 

[moon-buddy]

Interesting...thanks Rudy - I'll play around with that too.

But this is no different that having an * for the DB instance! If you only have 5 DB and all needs to be backed up, why go for listing the DB individually?

Listing them individually is the way to go if you are isolating - or not backing up - one or some DB.

 

[rore]

But this is no different that having an * for the DB instance! If you only have 5 DB and all needs to be backed up, why go for listing the DB individually?

Listing them individually is the way to go if you are isolating - or not backing up - one or some DB.

Ed,
I think you are confused about SQL instances and SQL databases.
* will backup all databases of the instance but not all instances.

Listing the databases instead of * is, in my opinion, a better practice. You will know what are you backing up (and what not). The DBA is responsible to inform if there is new databases to be backed up. Or you will know if there is databases deleted and that DBA didn't tell you.

Just my 2 cents,
Rudy

 

[dms19]

Rudy that seems to be where the confusion is coming in, the difference between a SQL instance (aka Sql server) vs SQL databases under and instance.

The multi cfg file works, but I'd like to try out the method you pointed out as well.

 

[moon-buddy]

Ed,
I think you are confused about SQL instances and SQL databases.
* will backup all databases of the instance but not all instances.

Listing the databases instead of * is, in my opinion, a better practice. You will know what are you backing up (and what not). The DBA is responsible to inform if there is new databases to be backed up. Or you will know if there is databases deleted and that DBA didn't tell you.

Just my 2 cents,
Rudy

I honestly don't think that by using * and listing the DB individually makes and difference from an operational viewpoint.

I have used both but more precisely, I have used a DB listing only if I know that there are DBs that does not need to be backed up.

As I have said, in dsm19's case, all 5 DBs need backing up. An * would do.

But again, this is just my two cents.

 

[dms19]

I need all 5 db's on each different SQL server. A simple * will only get the 5 under the default instance.

 

[rore]

I Honestly don't think that by using * and listing the DB individually makes and difference from an operational viewpoint.

I have used both but more precisely, I have used a DB listing only if I know that there are DBs that does not need to be backed up.

As I have said, in dsm19's case, all 5 DBs need backing up. An * would do.

But again, this is just my two cents.

Sorry to contradict but an * would not do.
Databases are in different SQL instances.

Well, there is no difference from a operational viewpoint but I prefer (and this is just an opinion) to know exactly what I am backing up (and consequently what not), instead of "I am backing up all" (what's all?).

Rudy

 

[rm333]

This script works flawlessly in our site:





set inst.1=<SQLSERVER\Instance1>
set inst.2=<SQLSERVER\Instance2>
set inst.3=<SQLSERVER\Instance3>
set inst.4=<SQLSERVER\Instance4>



rem ==================================================================
rem Replace "C:" with the drive where Data Protection for SQL
rem is installed. Update the directory to match the installation
rem directory that you chose when you installed the product.
rem ==================================================================
set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql
C:
cd %sql_dir%

rem ==================================================================
rem The two lines below put a date/time stamp in a log file for you.
rem Note: You can change "sqlsched.log" to whatever you prefer in
rem lines below.
rem ==================================================================
date < NUL >> %sql_dir%\sqlsched.log
time < NUL >> %sql_dir%\sqlsched.log
rem ==================================================================
rem Now call the command-line interface to do the backup:
rem
rem Replace "srvrname" with the name of the options file name you
rem plan to use.
rem
rem If SQL authentication is being used and the SQL login settings have
rem not been stored via the GUI, you must also specify the /sqluser and
rem /sqlpassword options on the command below.
rem
rem In this example, we use the '*' to back up all of the databases
rem on the SQL server. Note that database 'tempdb' will not
rem be backed up.
rem
rem Note: You can change "sqlsched.log" and "sqlfull.log" to
rem whatever you prefer.
rem ==================================================================

FOR /F "tokens=2* delims=.=" %%A IN ('set inst.') DO %sql_dir%\tdpsqlc backup * log /sqlserver=%%B /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqllog.log >> %sql_dir%\sqlsched.log & %sql_dir%\tdpsqlc backup * full /sqlserver=%%B /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqlfull.log >> %sql_dir%\sqlsched.log