?
Solved

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

Posted on 2008-11-07
16
Medium Priority
?
1,014 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:sk_raja_raja
  • 5
  • 2
  • 2
  • +4
14 Comments
 
LVL 15

Accepted Solution

by:
MohammedU earned 300 total points
ID: 22909385
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
 
LVL 18

Author Comment

by:sk_raja_raja
ID: 22909465
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
 
LVL 15

Expert Comment

by:MohammedU
ID: 22909524
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 18

Author Comment

by:sk_raja_raja
ID: 22923324
ok...what do you mean by connecting here ? Authentication ?????
0
 
LVL 18

Author Comment

by:sk_raja_raja
ID: 22976215
any help on this .....
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 22976603
Did you ever thought in changing backup software?
There are some few that can do what you want.
0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 300 total points
ID: 22976704
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
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 300 total points
ID: 23005146
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23005153
0
 
LVL 18

Author Comment

by:sk_raja_raja
ID: 23046663
No, i cannot change the backup software...it is not possible
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 300 total points
ID: 23132803
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
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 300 total points
ID: 23140334
>>  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
 
LVL 71

Expert Comment

by:Qlemo
ID: 23140650
fully agreed ;-)
0
 
LVL 18

Author Closing Comment

by:sk_raja_raja
ID: 31514494
Thanks all
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
The viewer will learn how to dynamically set the form action using jQuery.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question