Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Liverpool, England
    Posts
    18

    Smile Unanswered: Help scripting backup devices

    Hi guys

    I am relatively new to SQL Server admin and I have been handed a task of creating backup devices for a particular SQL Server which has 204 databases.

    Rather than go through and create Full, Incramental and Transaction Log backup devices for each database is ther anyway of doing the following

    Create a script to go through and for each database name in 'sysdatabases'

    create a folder e.g \sqldata\backup\%databasename%

    and within each folder create a backup device called

    %databasename%_full
    %databasename%_inc
    %databasename%_log

    Also all these databases are running in 'Simple' recovery mode so obviously I need to change this to 'Full' to enable incramental and log backups - is this possible using the same script.

    Hope someone can help as the thought of doing all of this individually for each database scares me silly!!!

    Thanks in advance for any help

    Hanley

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    not sure if this is an option but could you create a database maintenance plan inwhich the option 'All databases' is set and does the backup?

  3. #3
    Join Date
    Aug 2003
    Location
    Liverpool, England
    Posts
    18
    I could do that but I need to create the backup devices first, that is my problem, I need to automate the creation of 3 backup devices for each database (240 of them)


  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is 100% untested, but it should give you an idea or two:
    Code:
    DECLARE @cDb		sysname
    
    DECLARE zDb CURSOR FOR SELECT
       sd.name
       FROM master.dbo.sysdatabases AS sd
    
    OPEN zDb
    FETCH zDb INTO @cDb
    
    WHILE 0 = @@fetch_status
       BEGIN
          EXECUTE ('EXECUTE master.dbo.xp_cmdshell ''mkdir z:\sqldata\backup\'
    +        @cDb + '''')
    
          EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', ''' 
    +        @cDb + '_full'', ''z:\sqldata\backup\' + @cDb + '_full.dmp''')
          EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', ''' 
    +        @cDb + '_inc'', ''z:\sqldata\backup\' + @cDb + '_inc.dmp''')
          EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', ''' 
    +        @cDb + '_log'', ''z:\sqldata\backup\' + @cDb + '_log.dmp''')
    
          EXECUTE ('ALTER DATABASE ' + @cDb + ' SET RECOVERY FULL')
    
          FETCH zDb INTO @cDb
       END
    
    CLOSE zDb
    DEALLOCATE zDb
    -PatP

  5. #5
    Join Date
    Aug 2003
    Location
    Liverpool, England
    Posts
    18
    Pat P

    Thanks very much for that

    I'll give it a try on Monday

    Much appreciated


  6. #6
    Join Date
    Aug 2003
    Location
    Liverpool, England
    Posts
    18
    Pat P

    Script was successful, exactly what I was looking for.

    Many thanks

    Hanley


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •