Backup Multiple Databases?

Posted on 2005-04-08
Last Modified: 2008-02-01
Howdy Experts!

I have a SQL Server 2000 installation with close to a hundred different databases on it.  I would like to create a SQL query that will simply go through and backup all of the databases to my DVD writer.  I am stuck however on how to get the database names into my backup query....can you help?

Here is what I have so far.  The db_name is what I need to 'variablize'.  Thanks!

set quoted_identifier off
declare @backupquery varchar(1000)
set @backupquery = 'backup database '+ db_name() + ' to disk = "f:\data\'+ db_name() +'_'+convert(varchar(10),getdate(),112)+'.bak" with init '
print @backupquery
exec (@backupquery)
Question by:Thorin
    LVL 42

    Expert Comment

    create cursore
    and use to get db names in @variable:
    select name from master.dbo.sysdatabases

    also you can check:
    LVL 2

    Author Comment

    I am sorry...I am not super experienced with Cursors.  Can you provide more details on this solution?  Thanks.
    LVL 42

    Accepted Solution



    Set NoCount On

          @vCount            Int,
          @vNumDBs      Int,
          @vDBName      Varchar(255),
          @vBackupPath      Varchar(255),
          @vFileName      Varchar(100),
          @vCreateString      Varchar(1000),
          @vBackupString      Varchar(1000),
          @vDeleteString      Varchar(1000),
          @vNewPath      Varchar(1000),
          @vErrorString      Varchar(1000)

    Set @vBackupPath = '\\IDIBackup\SQL_Dev'

          @vDBList      Table(
                            DBID INT NOT NULL IDENTITY(1, 1),
                            DatabaseName      Varchar(256)
    Insert Into @vDBList
    Select Name From master.dbo.SysDatabases Where Name Not IN ('TempDB')
    Set @vNumDBs = @@RowCount
    Set @vCount = 1

    While @vCount < @vNumDBs
                Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount
                Set @vNewpath = @vBackupPath + '\' + @vDBName + '\'
                Set @vDeleteString = 'Del "' + @vNewPath + @vDBName + ' ' + Convert(Varchar(12), DateAdd(Day, -3, GetDate()), 104) + '.bak"' + ' /F /Q'
                Exec xp_CMDShell @vDeleteString, NO_OUTPUT
                Set @vCreateString = 'MD ' + @vNewPath
                Exec xp_CMDShell @vCreateString, NO_OUTPUT
                Set @vFileName =  @vDBName + ' ' + Convert(Varchar(12), GetDate(), 104) + '.bak'
                Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + @vFileName + ''' WITH  INIT ,  NOUNLOAD ,  NAME = N''' + @vDBName + ''',  NOSKIP ,  STATS = 50,  NOFORMAT'
    --Print @vBackupString
                Exec (@vBackupString)
                If @@Error <> 0
                            Set @vCount = @vNumDBs
                            Set @vErrorString = 'net send * SQL Backup failed on (' + @vDBName + ')! Please view event log!'
                            Exec XP_CMDShell @vErrorString
                Set @vCount = @vCount + 1

    LVL 2

    Author Comment

    Wahooo!  Thank you very much, that worked perfectly!  Once again, Experts Exchange helps to make me look good!  THANK YOU!
    LVL 42

    Expert Comment

    you are always welcome!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 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

    9 Experts available now in Live!

    Get 1:1 Help Now