asked on

backup multiple databases

Hi,
I have multiple identical databases on sql server 2008 R2 Server. Identical means every database has different name but the structure and tables are all same. Twice a month, I've to generate a report, and to generate that report i select each database and run the below script on sql server to findout the activities on those database tables one by one. for example:

USE [dBase1]
GO

Select MAX(DateOfLastUpdate) AS lastUpdate1
from Table1

Select MAX(DateOfLastUpdate) AS lastUpdate2
from Table2

Select MAX(DateOfLastUpdate) as lastUpdate3
from Table3

Open in new window

after that, i write down the results in a sheet and then create the report on excel sheet with database's name. is there a way, i can run the script just one time, not to select different names of databases and generate the report of all the databases at one time.
Thanks,

Steve Wales

There's an undocumented stored procedure called sp_msforeachdb that would cover this, as long as you want to run it for each and every DB in your instance.

Maybe you could setup a temp table and insert the results of each of those selects into the temp table with the database name as the first column.

You'd need to create an output table with 2 columns, for db_name and your date.

Then create your stored procedure to populate the output table:

insert into outputtable
Select DB_NAME(), MAX(DateOfLastUpdate) AS lastUpdate1
from Table1

insert into outputtable
Select DB_NAME(), MAX(DateOfLastUpdate) AS lastUpdate2
from Table2

insert into outputtable
Select DB_NAME(), MAX(DateOfLastUpdate) as lastUpdate3
from Table3

Then in the calling procedure, truncate the table at the beginning and call sp_msforeachdb

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC my_reporting_proc'

Then at the end of the calling proc, do what you want with outputtable for reporting your output.

TempDBA

Yes you can do it. There is an undocumented stored procedure for that. Here is a link to the definition with example:-
http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

plusone3055

watch these videos part 1 and 2
Part 1
http://www.youtube.com/watch?v=5IfigBLubGU

part 2
http://www.youtube.com/watch?v=bTz3OZKTdMg

WaibelRD

This may work for you. You will want to un-REM the Last EXEC(@SQL) statement to actually populate the ##Results table.

Declare @@DBName sysname, @@TblName sysname
Declare @SQL varchar(max)

SET NOCOUNT ON

Create Table ##Results (
DBName varchar(256)
,tblName varchar(256)
,LastUpdate smalldatetime
)

Declare DBList cursor for
select name from sys.databases where database_id > 4
Open DBList
Fetch next from DBLIST into @@DBName
while @@FETCH_STATUS = 0 BEGIN
if exists(Select * From tempdb.sys.objects Where name='##Temp')
Drop Table ##Temp

Select @SQL = 'Select name INTO ##TEMP from '+ @@DBName +'.sys.tables where type=''U'''
EXEC(@SQL)
if exists(Select * From tempdb.sys.objects Where name='##Temp') BEGIN
Declare TBLList cursor for
Select Name From ##Temp
Open TBLList
Fetch Next from TBLList into @@Tblname
While @@FETCH_STATUS = 0 begin
Select @SQL = 'Insert Into ##Results Select ''' + @@DBName + ''',''' + @@Tblname + ''', MAX(DateOfLastUpdate) From '+ @@DBName +'..[' + @@Tblname + ']'
print @SQL
--exec(@SQL) -- Un-REM to use
Fetch Next from TBLList into @@Tblname
end
close TBLList
Deallocate TBLLIst
END
Fetch next from DBLIST into @@DBName
END
Close DBList
Deallocate DBList

Select * From ##Results
Drop Table ##Results

EugeneZ

there are several ways

you can try :to use:

use DB3
create table dbo.tempresult  (lastUpdate1 datetime,lastUpdate2 datetime,lastUpdate3 datetime)
 exec master..sp_msforeachdb 'if (''?''not  in (''master'',''msdb'',''model'',''tempdb'') 
begin insert into DB3.dbo.tempresult select ''?'' DBName, (Select MAX(DateOfLastUpdate)from ?.dbo.Table1 ) AS lastUpdate1, (Select MAX(DateOfLastUpdate)   from ?.dbo.Table2 ) AS lastUpdate2,          (Select MAX(DateOfLastUpdate)  from ?.dbo.Table3 ) as lastUpdate3'

selecty * from DB3.dbo.tempresult

Open in new window

or just like this

select 'db1' DBName, (Select MAX(DateOfLastUpdate)from db1.dbo.Table1 ) AS lastUpdate1, (Select MAX(DateOfLastUpdate)   from db1.dbo.Table2 ) AS lastUpdate2,          (Select MAX(DateOfLastUpdate)  from db1.dbo.Table3 ) as lastUpdate3
union aLL

select 'db2' DBName, (Select MAX(DateOfLastUpdate)from db2.dbo.Table1 ) AS lastUpdate1, (Select MAX(DateOfLastUpdate)   from db2.dbo.Table2 ) AS lastUpdate2,          (Select MAX(DateOfLastUpdate)  from db2.dbo.Table3 ) as lastUpdate3

union aLL

select 'db3' DBName, (Select MAX(DateOfLastUpdate)from db3.dbo.Table1 ) AS lastUpdate1, (Select MAX(DateOfLastUpdate)   from db3.dbo.Table2 ) AS lastUpdate2,          (Select MAX(DateOfLastUpdate)  from db3.dbo.Table3 ) as lastUpdate3

Open in new window

jogos

How to is shown by previous experts. Want to add a little safety to those solutions.

You can have a list of databases given or excluded but ultimatly there can be an error because there is one database where you don't have enough permision, that table does not exists or an extra column is not added yet .....

So adding the TRY CATCH to your execution per database will prevent one database giving an error to have no result for all other databases. .... But be sure you also capture that there was an error.
http://msdn.microsoft.com/en-us/library/ms175976.aspx

Alpesh Patel

You can use maintenance plan for that and select the databases to backup and schedule that task. You can get this in SSMS -> Maitenance Plan node.

s_hausen

ASKER

Thx for everyone's input. but i've more than 800 databases here. writing script for each database won't help me. is there a way, i can run a loop or something??

Steve Wales

As posted in the first reponse that is what sp_msforeachdb does. It goes through each database in your instance and runs whatever commands you want it to run and applies those same commands on each DB.

Create a stored procedure that does what you want done on each DB.

Then do:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC my_reporting_proc'

It will run the procedure across each database in the instance.

Have a re-read of my first reply - and TempDBA's following post also references a blog posting giving examples.

s_hausen

ASKER

use DB3
create table dbo.tempresult  (lastUpdate1 datetime,lastUpdate2 datetime,lastUpdate3 datetime)
 exec master..sp_msforeachdb 'if (''?''not  in (''master'',''msdb'',''model'',''tempdb'') 
begin insert into DB3.dbo.tempresult select ''?'' DBName, (Select MAX(DateOfLastUpdate)from ?.dbo.Table1 ) AS lastUpdate1, (Select MAX(DateOfLastUpdate)   from ?.dbo.Table2 ) AS lastUpdate2,          (Select MAX(DateOfLastUpdate)  from ?.dbo.Table3 ) as lastUpdate3'

select * from DB3.dbo.tempresult

Open in new window

I ran the above code of EugeneZ, but some how, it's not inserting the data in table. any clue??

jogos

You have no matching END for the BEGIN in your command.

Corrected that error and added a catch so you have -1 for the databases where you got errors ....

use DB3
create table dbo.tempresult  (lastUpdate1 datetime,lastUpdate2 datetime,lastUpdate3 datetime)

DECLARE @SQLCMD VARCHAR(4000)
@SQLCMD = 'BEGIN TRY '
+ ' if (''?''not  in (''master'',''msdb'',''model'',''tempdb'') '
+ ' begin '
+ ' insert into DB3.dbo.tempresult select ''?'' DBName, '
+ ' (Select MAX(DateOfLastUpdate)from ?.dbo.Table1 ) AS lastUpdate1 '
+ ' ,(Select MAX(DateOfLastUpdate)   from ?.dbo.Table2 ) AS lastUpdate2 '
+ ' ,(Select MAX(DateOfLastUpdate)  from ?.dbo.Table3 ) as lastUpdate3 '
+ ' END'
+ ' END TRY '
+ ' BEGIN CATCH '
+ '   INSERT into DB3.dbo.tempresult select ''?'' DBName, -1,-1,-1 '
+ ' END CATCH '
 exec master..sp_msforeachdb @sqlCmd

select * from DB3.dbo.tempresult

Open in new window

s_hausen

ASKER

got this error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@SQLCMD'.
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@sqlCmd".

Steve Wales

On line 5 you need a SET infront... SET @SQLCMD
See: http://msdn.microsoft.com/en-us/library/ms189484.aspx

What is the collation of your Master database? It appears that the case sensitivity of the collation of the master database determines if your local variables in a T-SQL script are also case sensitive:
See: http://ntsblog.homedev.com.au/index.php/2008/12/04/case-sensitive-t-sql-tsql-variables/

Change @sqlCmd to @SQLCMD and I'm guessing it will work.

s_hausen

ASKER

when i run the below code it creates a table in my database and also insert the values in it.

EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'')
BEGIN

 SELECT name,physical_name,state,size 
 FROM ?.sys.database_files
 WHERE name LIKE ''?%''
END'

create table dbo.tempresult
 (
 name VARCHAR(50),
 physical_name VARCHAR(500),
 state BIT,
 size INT
 )

 INSERT INTO dbo.tempresult
EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
 SELECT DBName,Suvery,PoC,NNotes 
 FROM ?.sys.database_files
END'

Open in new window

but when i ran the final code below it does not enter the values in it. just confused why? am i doing something wrong selecting database??

EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       select ''?'' DBName,
       (Select MAX(DateOfLastUpdate) from DSS_Survey ) AS Suvery, 
       (Select MAX(DateOfLastUpdate) from PoC ) AS PoC,
       (Select MAX(DateOfLastUpdate) from NotesOfNurses ) as NNotes
END'
Create table dbo.tempresult
    (
      DBName VARCHAR(500),
      Suvery datetime,
      PoC datetime,
      NNotes datetime
    )

INSERT INTO dbo.tempresult
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT DBName,Suvery,PoC,NNotes 
       FROM ?.sys.database_files
END'

Open in new window

EugeneZ

try

EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
INSERT INTO dbo.tempresult
SELECT DBName,Suvery,PoC,NNotes
FROM ?.sys.database_files
END'

select * from dbo.tempresult

s_hausen

ASKER

i ran the code it works fine and create table but after that, code does not insert the values in the table and give a list of errors:

EXEC sp_MSforeachdb 'IF ''?''  NOT IN 
(''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''ClientsInfo'',''wheelsDemo'',''DB3'')
BEGIN
       select ''?'' DBName,
       (Select MAX(DateOfLastUpdate) from ?.dbo.DSS_Survey ) AS Suvery, 
       (Select MAX(DateOfLastUpdate) from ?.dbo.PoC ) AS PoC,
       (Select MAX(DateOfLastUpdate) from ?.dbo.NotesOfNurses ) as NNotes
END'
Create table dbo.tempresult
    (
      DBName VARCHAR(500),
      Suvery datetime,
      PoC datetime,
      NNotes datetime
    )

Open in new window

--AFTER CREATING TABLE, CODE GENERATES ERROR

Msg 207, Level 16, State 1, Line 5
Invalid column name 'DBName'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Suvery'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'PoC'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'NNotes'.

Open in new window

--BELOW, CODE GENERATES ERROR AND DATA DOES NOT GO INSIDE THE TABLE

EXEC sp_MSforeachdb 'IF ''?''  NOT IN 
(''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''ClientsInfo'',''wheelsDemo'',''DB3'')
BEGIN
INSERT INTO dbo.tempresult
SELECT DBName,Suvery,PoC,NNotes
       FROM ?.sys.database_files
END'

select * from dbo.tempresult

Open in new window

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

s_hausen

ASKER

finally, i got the solution.

EXEC sp_MSforeachdb 'IF ''?''  NOT IN 
(''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''ClientsInfo'',''Paradise''
,''TynetPayroll'',''dbMActivity'')
BEGIN
       select ''?'' DBName,
       (Select TOP 1 providername from ?.dbo.provider) as CompanyName,
       (Select MAX(CONVERT(VARCHAR(10),DateOfCreationOfRecord,101)) from ?.dbo.provider ) AS SetupDate,
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.DSS_Survey ) AS Suvery, 
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.PoC ) AS PoC,
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.NotesOfNurses ) as NNotes,
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.RAP ) AS InstBilling, 
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.RAPHMO ) AS HMOBilling,
       (SELECT Count (*) AS CurrentPatients FROM ?.dbo.PatientList 
       WHERE PatientDischarged <> "Discharged" OR PatientDischarged IS NULL) as CurrentPatients
END'

Create table tempActivity
    (
      DBName VARCHAR(500),
      CompanyName VARCHAR(500),
      SetupDate date,
      Suvery date,
      PoC date,
      NNotes date,
      InstBilling date,
      HMOBilling date,
      CurrentPatients int
    )

INSERT INTO tempActivity
EXEC sp_MSforeachdb 'IF ''?''  NOT IN
(''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''ClientsInfo'',''Paradise''
,''TynetPayroll'',''dbMActivity'')
BEGIN
      select ''?'' DBName,
       (Select TOP 1 providername from ?.dbo.provider) as CompanyName,
       (Select MAX(CONVERT(VARCHAR(10),DateOfCreationOfRecord,101)) from ?.dbo.provider ) AS SetupDate,
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.DSS_Survey ) AS Suvery, 
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.PoC ) AS PoC,
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.NotesOfNurses ) as NNotes,
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.RAP ) AS InstBilling, 
       (Select MAX(CONVERT(VARCHAR(10),DateOfLastUpdate,101)) from ?.dbo.RAPHMO ) AS HMOBilling,
       (SELECT Count (*) AS CurrentPatients FROM ?.dbo.PatientList 
       WHERE PatientDischarged <> "Discharged" OR PatientDischarged IS NULL) as CurrentPatients
END'

Open in new window

thanks for everyone's help. i do appreciate it, all of you all life savers.