Solved

backup multiple databases

Posted on 2013-01-02
18
390 Views
Last Modified: 2013-01-04
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,
0
Comment
Question by:s_hausen
  • 6
  • 3
  • 3
  • +5
18 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38738095
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.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38738099
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
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 38738106
0
 
LVL 1

Expert Comment

by:WaibelRD
ID: 38738176
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
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38738593
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

0
 
LVL 25

Expert Comment

by:jogos
ID: 38738634
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
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38739109
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.
0
 

Author Comment

by:s_hausen
ID: 38744506
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??
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38744931
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:s_hausen
ID: 38745782
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??
0
 
LVL 25

Expert Comment

by:jogos
ID: 38745826
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

0
 

Author Comment

by:s_hausen
ID: 38745866
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".
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38745896
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.
0
 

Author Comment

by:s_hausen
ID: 38745979
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

0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38746054
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
0
 

Author Comment

by:s_hausen
ID: 38746091
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

0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 38746126
there are not such columns  in the sys.database_files sys view

run SELECT *      FROM sys.database_files


and adjust your code with columns you need
---
--like this one example from http://stackoverflow.com/questions/9630279/listing-information-about-all-database-files-in-sql-server

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files
'
Select * From ##temp
drop table ##temp

Open in new window

0
 

Author Comment

by:s_hausen
ID: 38746139
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now