Need a script to Backup multiple sql servers and sql instances or any other ways to do

Hello Experts,

I am using HP open view data protector as Backup software, unfortunately this application does not support backing up multiple sql instances or sql servers in a single backup job, I have have nearly 35 sql servers and 90 sql instances and its a kind of funny to create 90 sql jobs and scheduling each of them on a different time.

HP guys told me that i can prepare a script and run as a "Pre Exec and Post Exec" script on the backup job to backup multiple sql servers or sql instances on a single job with stopping and starting the sql services.

Please guide me to the right directon, Please throw me more queries and will provide you with all required information.
LVL 18
sk_raja_rajaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohammedUCommented:
Create a table on one of the server where it has access to all  SQL server instance and enter all the sql instance names in the table.
Create a script for all db backups...
Call the script through OSQL/SQLCMD and execute the backup script created in previous step and cursor through all instances...

Ex;
CREATE PROC Sp_Backup_AllDb  
@DbName sysname = null,  
@BackupPath Varchar(1000)  
AS  
SET NOCOUNT ON  
 /*  
 Syntax: exec Sp_Backup_AllDb  @DbName = 'pubs', @BackupPath = 'c:\'
Please use "@DbName = NULL" to run all db backups....  
*/  
if right(@BackupPath,1) <> '\'  
select @BackupPath = @BackupPath+'\'  
 
DECLARE @SQL VARCHAR(1000),  @DeviceName sysname  
 
IF @DbName is not null  
BEGIN  
 if NOT EXISTS  (select 1 from master..sysdatabases where name = ISNULL(@DbName,'%') )  
  BEGIN  
  select 'database does not exist ...Please run the sp_helpdb to get the list of the databases...'  
  return  
  END  
END  
 
IF @DbName is null  
BEGIN  
SELECT @DbName = '%'  
END  
 
BEGIN  
DECLARE DBCUR CURSOR FOR  
SELECT NAME FROM master..SYSDATABASES WHERE  
NAME NOT IN ('MODEL','TEMPDB','PUBS','NORTHWIND')  
and DATABASEPROPERTYEX(name, 'status')= 'online'  
and name like @DbName  
ORDER BY NAME  
OPEN DBCUR  
FETCH DBCUR INTO @DbName  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SELECT 'Backup started for '+ @DbName + ' Database'  
 BEGIN  
 SELECT @DeviceName = @BackupPath+'backup_'+replace (CONVERT(Varchar(100), SERVERPROPERTY('servername')) ,'\','$')+'_'+ @DbName+'.bak'    
 SELECT @SQL = 'EXEC MASTER..XP_CMDSHELL ''del '+@DeviceName+''', no_output'  
 EXEC ( @SQL )  
 BACKUP DATABASE @DbName TO disk =  @DeviceName WITH  INIT ,  NOUNLOAD, SKIP ,  STATS = 10,  NOFORMAT  
 
END  
 
 
 
FETCH DBCUR INTO @DbName  
END  
CLOSE DBCUR  
DEALLOCATE DBCUR  
end  
 
 



GO
CREATE TABLE [SQL_Server_List] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [SQL_Instance_Name] [sysname] NOT NULL
) ON [PRIMARY]
GO



declare @SQL_Instance_Name sysname, @sql Varchar(1000)  
DECLARE SQL_Instance CURSOR   For  
select SQL_Instance_Name from Admin.dbo.SQL_Server_List order by SQL_Instance_Name  
OPEN SQL_Instance  
FETCH NEXT FROM SQL_Instance into @SQL_Instance_Name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
select @sql = 'osql -S'+ @SQL_Instance_Name+' -d master -E -ic:\backupscript.sql -n '  
select @sql  
exec master.dbo.xp_cmdshell @sql  
FETCH NEXT FROM SQL_Instance into @SQL_Instance_Name  
end  
close SQL_Instance  
deallocate SQL_Instance  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sk_raja_rajaAuthor Commented:
i am a newbie here and i need more detailedd explanation..i am sorry
Also i will not be able to do anything on the sql server, like creating tables or stored procedures...etc... i can just create a batch file and link it to HP data protector backup job post exec
0
MohammedUCommented:
the script I have posted has complete solution but without connecting it is hard for you to get all the instance info unless you write a c# or VB script and pass the instance names from the text file...

I sorry! but I don't have the solution better than this... at this time...
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

sk_raja_rajaAuthor Commented:
ok...what do you mean by connecting here ? Authentication ?????
0
sk_raja_rajaAuthor Commented:
any help on this .....
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you ever thought in changing backup software?
There are some few that can do what you want.
0
Daniel WilsonCommented:
I think MohammedU is referring to connecting to the SQL Browser service.  Since he doesn't have code to do that, he suggests keeping the list of instance names in a text file ... maybe in the script file that does the work.

I think it's a good idea.
0
ZberteocCommented:
You could try the sqlcmd utility that comes with the SQL 2005 so it is already installed an your 2005 servers. WHat you need is a source for your sql server instances and the a dynamic query backup script in a file that will be executed by sqlcmd in a loop against all the instances found in that source. It could be simple text file. You would need to build a batch file to do that for you and then create a windows schedule for it.
0
ZberteocCommented:
0
sk_raja_rajaAuthor Commented:
No, i cannot change the backup software...it is not possible
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
IMHO, the only usable solution is:

Defining the backup jobs on each instance itself (in a MSSQL Mainenance Plan executed by SQL Agent service), putting the backup on a local or network disk (latter could be problematic because of service account and network access rights).
The same can be done by Task Scheduler with a small SQL batch file.
At some time, start the HP backup job making a file backup instead of a database backup.

Caveat: you have to provide enougth place for all your backups, that might be very very much. And you have no clue when all backups are finished for sure.



0
Jim P.Commented:
>>  script on the backup job to backup multiple sql servers or sql
>> instances on a single job with stopping and starting the sql services.

What that is indicating to me is that they want to shut down each individual SQL Service on each server and for each instance and then do a backup of the mdf/ldf files and then re-start the instance.

The first question is: What drugs is the HP rep taking!?  And can I get some?

How large a company are you to need 35 servers and 90 instances? Can they be consolidated at all?

Now after the rhetorical, mind-boggling, questions are asked:

MohammedU and Qlemo are in the ballpark. Really what you need to do is first evaluate what databases you really need to backup and how often. You will want the master and msdb from each DB. The Model db is generally never needed and tempdb is not backed up.  Then out of your user databases you need to determine which actually need to backed up.

After you get that down, I would back up to disk and then from there have the HP Backup sweep those locations. It is fairly hard to get SQL to backup its data to anywhere but the local server. So you may want to look at some kind of shared SAN pool that each server can attach to and then backup the SAN. No matter which way you do it, it will be disk space and speed intensive.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
fully agreed ;-)
0
sk_raja_rajaAuthor Commented:
Thanks all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.