locked
T-SQL RRS feed

  • Question

  • I am using the following code where only one DB name can be declared at 'Database Here',what would be

    the ideal way to change the code to declare multiple values at one go?

    Any suggestions?



    DECLARE@DBName VARCHAR(128) = 'DATABASE HERE', 

     
            @DriveLetter VARCHAR(100),
            @FolderName VARCHAR(100)
     
     SELECT @DriveLetter = [DriveLetter01Name],
           @FolderName = [FolderName]
    FROM [dba].dbo.[dbaDatabaseBackupDrive]
    WHERE [DatabaseName] IS NULL;
    IF
    (
        SELECT COUNT(1)
        FROM [dba].[dbo].[dbaDatabaseBackupDrive]
        WHERE [DatabaseName] = @DBName
    ) = 0
    BEGIN
        INSERT INTO [dba].[dbo].[dbaDatabaseBackupDrive]
        (
            [DatabaseName],
            [DriveLetter01Name],
            [FolderName],
            [ExcludeBackupInd],
            [CommentsTxt]
        )
        SELECT name,
               @DriveLetter,
               @FolderName,
               1,
               'Excluded by: ' + SUSER_NAME()
        FROM sys.sysdatabases
        WHERE [name] = @DBName;
    END;
    ELSE
        PRINT 'Database ' + @DBName + ' is already excluded from backup';
     
    Friday, June 5, 2020 8:47 AM

Answers

  • Here is yet one more solution. It should work on SQL 2005 and up.

    DECLARE @DBNames TABLE (dbname sysname NOT NULL PRIMARY KEY)
    INSERT @DBNames(dbname) VALUES
        ('Dataase 1 HERE'),
        ('Dataase 2 HERE')
        --etc

    DECLARE @DriveLetter VARCHAR(100),
            @FolderName VARCHAR(100),
            @list        nvarchar(MAX)
     
     SELECT @DriveLetter = [DriveLetter01Name],
            @FolderName = [FolderName]
    FROM   [dba].dbo.[dbaDatabaseBackupDrive]
    WHERE   [DatabaseName] IS NULL;

    SELECT @list =     (SELECT dbname + ' '
         FROM   @DBNames d
         WHERE  EXISTS (SELECT *
                        FROM [dba].[dbo].[dbaDatabaseBackupDrive] bd
                        WHERE bd.[DatabaseName] = d.dbname)
          FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    INSERT INTO [dba].[dbo].[dbaDatabaseBackupDrive]
        (
            [DatabaseName],
            [DriveLetter01Name],
            [FolderName],
            [ExcludeBackupInd],
            [CommentsTxt]
        )
    SELECT name, @DriveLetter, @FolderName, 1, 'Excluded by: ' + SUSER_NAME()
    FROM   sys.sysdatabases d
    WHERE  EXISTS (SELECT *
                   FROM   @DBNames n
                   WHERE  n.dbname = d.name)
      AND  NOT EXISTS (SELECT *
                       FROM   [dba].[dbo].[dbaDatabaseBackupDrive] bd
                       WHERE  bd.[DatabaseName] = d.name)

    IF @list IS NOT NULL
       PRINT 'Database(s) ' + @list + ' already excluded from backup';


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by saravana raj Monday, June 8, 2020 5:18 AM
    Saturday, June 6, 2020 12:25 PM

All replies

  • DECLARE @DBName table(dbname VARCHAR(128))

    insert @dbname

    values( 'DATABASE HERE'),('second db here')

     declare @DriveLetter VARCHAR(100),
            @FolderName VARCHAR(100)
     
     SELECT @DriveLetter = [DriveLetter01Name],
           @FolderName = [FolderName]
    FROM [dba].dbo.[dbaDatabaseBackupDrive]
    WHERE [DatabaseName] IS NULL;

    select 'message here for ' d.dbname

    FROM @dbname d

    inner join sys.sysdatabases sd  on sd.name=d.dbname



        INSERT INTO [dba].[dbo].[dbaDatabaseBackupDrive]
        (
            [DatabaseName],
            [DriveLetter01Name],
            [FolderName],
            [ExcludeBackupInd],
            [CommentsTxt]
        )
        SELECT name,
               @DriveLetter,
               @FolderName,
               1,
               'Excluded by: ' + SUSER_NAME()
        FROM @dbname d

    left join sys.sysdatabases sd  on sd.name=d.dbname

    where sd.name is null




    • Edited by Curendra Friday, June 5, 2020 9:05 AM
    Friday, June 5, 2020 9:00 AM
  • It selects the 2 values but the insertion doesn't happen!

    Thanks for the reply!

    Friday, June 5, 2020 11:10 AM
  • I used sys.databases instead of dbadatabasebackup. use below code.

    DECLARE @DBName table(dbname VARCHAR(128))

    insert @dbname

    values( 'DATABASE HERE'),('second db here')

     declare @DriveLetter VARCHAR(100),
            @FolderName VARCHAR(100)
     
     SELECT @DriveLetter = [DriveLetter01Name],
           @FolderName = [FolderName]
    FROM [dba].dbo.[dbaDatabaseBackupDrive]
    WHERE [DatabaseName] IS NULL;

    select 'message here for ' d.dbname

    FROM @dbname d

    inner join [dba].[dbo].[dbaDatabaseBackupDrive] sd  on sd.DatabaseName=d.dbname



        INSERT INTO [dba].[dbo].[dbaDatabaseBackupDrive]
        (
            [DatabaseName],
            [DriveLetter01Name],
            [FolderName],
            [ExcludeBackupInd],
            [CommentsTxt]
        )
        SELECT name,
               @DriveLetter,
               @FolderName,
               1,
               'Excluded by: ' + SUSER_NAME()
        FROM @dbname d

    left join [dba].[dbo].[dbaDatabaseBackupDrive] sd  on sd.DatabaseName=d.dbname

    where sd.DatabaseName is null

    Friday, June 5, 2020 11:24 AM
  • The difficulty with multiple is this message:

    PRINT 'Database ' + @DBName + ' is already excluded from backup';

    Do you really need this message for each database which is already excluded?

    What version of SQL Server are you using?

    Friday, June 5, 2020 2:39 PM
  • If you are using SQL 2016+, this should work:

    DECLARE @DBNames VARCHAR(max) = 'DATABASE1, DATABASE2, DATABASE3' 
    
        INSERT INTO [dba].[dbo].[dbaDatabaseBackupDrive]
        (
            [DatabaseName],
            [DriveLetter01Name],
            [FolderName],
            [ExcludeBackupInd],
            [CommentsTxt]
        )
        SELECT name,
               backupdrive.DriveLetter,
               backupdrive.FolderName,
               1,
               'Excluded by: ' + SUSER_NAME()
        FROM sys.sysdatabases d
    		LEFT OUTER JOIN [dba].[dbo].[dbaDatabaseBackupDrive] backupdrive
    		ON backupdrive.[DatabaseName] = d.[name]
    
        WHERE d.[name] IN (SELECT * FROM string_split(@dbnames,','))
    		AND d.[name] NOT IN (SELECT [DatabaseName] FROM  [dba].[dbo].[dbaDatabaseBackupDrive])	-- Exclude existing


    Friday, June 5, 2020 2:47 PM
  • Here is yet one more solution. It should work on SQL 2005 and up.

    DECLARE @DBNames TABLE (dbname sysname NOT NULL PRIMARY KEY)
    INSERT @DBNames(dbname) VALUES
        ('Dataase 1 HERE'),
        ('Dataase 2 HERE')
        --etc

    DECLARE @DriveLetter VARCHAR(100),
            @FolderName VARCHAR(100),
            @list        nvarchar(MAX)
     
     SELECT @DriveLetter = [DriveLetter01Name],
            @FolderName = [FolderName]
    FROM   [dba].dbo.[dbaDatabaseBackupDrive]
    WHERE   [DatabaseName] IS NULL;

    SELECT @list =     (SELECT dbname + ' '
         FROM   @DBNames d
         WHERE  EXISTS (SELECT *
                        FROM [dba].[dbo].[dbaDatabaseBackupDrive] bd
                        WHERE bd.[DatabaseName] = d.dbname)
          FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

    INSERT INTO [dba].[dbo].[dbaDatabaseBackupDrive]
        (
            [DatabaseName],
            [DriveLetter01Name],
            [FolderName],
            [ExcludeBackupInd],
            [CommentsTxt]
        )
    SELECT name, @DriveLetter, @FolderName, 1, 'Excluded by: ' + SUSER_NAME()
    FROM   sys.sysdatabases d
    WHERE  EXISTS (SELECT *
                   FROM   @DBNames n
                   WHERE  n.dbname = d.name)
      AND  NOT EXISTS (SELECT *
                       FROM   [dba].[dbo].[dbaDatabaseBackupDrive] bd
                       WHERE  bd.[DatabaseName] = d.name)

    IF @list IS NOT NULL
       PRINT 'Database(s) ' + @list + ' already excluded from backup';


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by saravana raj Monday, June 8, 2020 5:18 AM
    Saturday, June 6, 2020 12:25 PM
  • Thank you.!!
    Monday, June 8, 2020 6:23 AM