#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    SP for Database Stats, Size, Backup, etc.


    Hey guys, first post but read here often.

    Trying to put together a SP for daily collection of [db name, db file name, db size, backup size, backup date, etc.]

    Here's my table

    Code:
    CREATE TABLE [dbo].[BackupStatus](
    	[addDate] [datetime] NOT NULL,
    	[name] [nvarchar](60) NOT NULL,
    	[bu_name] [nvarchar](60) NOT NULL,
    	[phy_name] [nvarchar](60) NOT NULL,
    	[state] [nvarchar](60) NOT NULL,
    	[bu_size] [numeric](20, 0) NOT NULL,
    	[db_size] [numeric](20, 0) NOT NULL,
    	[db_id] [numeric](3, 0) NOT NULL,
    	[recovery_model] [nvarchar](20) NOT NULL,
    	[recovery_model_desc] [nvarchar](20) NOT NULL,
    	[page_verify] [nvarchar](20) NOT NULL,
    	[backup_start_date] [datetime] NOT NULL,
    	[backup_stop_date] [datetime] NOT NULL,
     CONSTRAINT [PK_BackupStatus_1] PRIMARY KEY CLUSTERED 
    (
    	[addDate] DESC,
    	[name] ASC,
    	[bu_name] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    Here's my Query

    Code:
    INSERT INTO SERVER_X.db_backup_status.dbo.BackupStatus ([addDate]
    	  ,[name]
          ,[bu_name]
          ,[phy_name]
          ,[state]
          ,[bu_size]
          ,[db_size]
          ,[db_id]
          ,[recovery_model]
          ,[recovery_model_desc]
          ,[page_verify]
          ,[backup_start_date]
          ,[backup_stop_date])
    SELECT bs.backup_start_date as 'addDate', 
    sysdb.name as 'name', ISNULL(bs.name,'Null') as 'bu_name', 
    sysmf.physical_name as 'phy_name', 
    sysmf.state_desc as 'state', 
    bs.backup_size as 'bu_size', 
    sysmf.size as 'db_size', 
    sysdb.database_id as 'db_id', 
    bs.recovery_model as 'recovery_model',     
    sysdb.recovery_model_desc as 'recovery_model_desc', 
    sysdb.page_verify_option_desc as 'page_verify', 
    bs.backup_start_date as 'backup_start_date', 
    bs.backup_finish_date as 'backup_stop_date'   
    FROM sys.databases sysdb, sys.master_files sysmf, SERVER_X.msdb.dbo.backupset bs    
    WHERE sysdb.name = bs.database_Name   and sysmf.database_id = sysdb.database_id
    Error
    Msg 102, Level 15, State 1, Line 16 Incorrect syntax near 'addDate'.
    Please help. I've tried going from SELECT (XX,XY,YY) INTO, to also INSERT (XX,XY,YY) INTO... Can't seem to get it right.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    i just noticed that you put to much brackets on your query, hehehe, and you put to much aliases.

    why not remove the alias? I think it is not necessary because you have declared them to be inserted on their specific location in your insert into statement.

    Hope This Helps
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0
    Sorry for the delay.

    Thanks narrokk. I can query without issue, it's believe it's just my formatting. I can't seem to figure out what's wrong.

    I am going back through to find out if I made sure the tables are the same column format as the data I'm getting.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by quistlogic
    Sorry for the delay.

    Thanks narrokk. I can query without issue, it's believe it's just my formatting. I can't seem to figure out what's wrong.

    I am going back through to find out if I made sure the tables are the same column format as the data I'm getting.
    i just saw this in your code

    INSERT INTO SERVER_X.db_backup_status.dbo.BackupStatus

    could you try something like

    INSERT INTO dbo.BackupStatus

    or something like

    INSERT INTO SERVER_X.dbo.BackupStatus

    and also, are you using sql server? or another type of Server Manager?

    If i'm right, we might be on a different boat
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    Thumbs up


    Solved. Kind of. It still hits an issue with Primary Key, but I believe that was from old tests where they included datetime with time being stripped.


    TABLE:
    Code:
    USE [db_backup_status]
    GO
    
    /****** Object:  Table [dbo].[BackupStatus]    Script Date: 11/11/2012 23:29:19 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BackupStatus]') AND type in (N'U'))
    DROP TABLE [dbo].[BackupStatus]
    GO
    
    USE [db_backup_status]
    GO
    
    /****** Object:  Table [dbo].[BackupStatus]    Script Date: 11/11/2012 23:29:19 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[BackupStatus](
    	[addDate] [datetime] NOT NULL,
    	[name] [sysname] NOT NULL,
    	[bu_name] [nvarchar](60) NOT NULL,
    	[phy_name] [nvarchar](260) NOT NULL,
    	[state] [nvarchar](60) NOT NULL,
    	[bu_size] [numeric](20,0) NOT NULL,
    	[db_size] [int] NOT NULL,
    	[db_id] [int] NOT NULL,
    	[recovery_model] [nvarchar](60) NOT NULL,
    	[recovery_model_desc] [nvarchar](60) NOT NULL,
    	[page_verify] [nvarchar](60) NOT NULL,
    	[backup_start_date] [datetime] NOT NULL,
    	[backup_stop_date] [datetime] NOT NULL,
     CONSTRAINT [PK_BackupStatus_addDate-name-bu_name] PRIMARY KEY CLUSTERED 
    (
    	[addDate] ASC,
    	[name] ASC,
    	[phy_name] ASC,
    	[bu_size] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    QUERY:
    Code:
    INSERT INTO SERVER-X.db_backup_status.dbo.BackupStatus (
    	   [addDate]
    	  ,[name]
          ,[bu_name]
          ,[phy_name]
          ,[state]
          ,[bu_size]
          ,[db_size]
          ,[db_id]
          ,[recovery_model]
          ,[recovery_model_desc]
          ,[page_verify]
          ,[backup_start_date]
          ,[backup_stop_date])
    SELECT bs.backup_start_date, 
    sysdb.name, 
    ISNULL(bs.name,'Null'), 
    sysmf.physical_name, 
    sysmf.state_desc, 
    bs.backup_size, 
    sysmf.size, 
    sysdb.database_id,
    bs.recovery_model,     
    sysdb.recovery_model_desc, 
    sysdb.page_verify_option_desc, 
    bs.backup_start_date,
    bs.backup_finish_date 
    FROM sys.databases sysdb, sys.master_files sysmf, SERVER-X.msdb.dbo.backupset bs    
    WHERE sysdb.name = bs.database_Name   and sysmf.database_id = sysdb.database_id
    AND bs.backup_start_date >= '01-01-2012'

IMN logo majestic logo threadwatch logo seochat tools logo