backup multiple databases

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.

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

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

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

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

Select allOpen 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

Select allOpen in new window

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

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.

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??

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.

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

Select allOpen in new window

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

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

Select allOpen in new window

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".

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.

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'

Select allOpen 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'

Select allOpen in new window

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

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
    )

Select allOpen 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'.

Select allOpen 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

Select allOpen in new window

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'

Select allOpen in new window

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