[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Backup Multiple Databases?

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)
0
Thorin
Asked:
Thorin
  • 3
  • 2
1 Solution
 
Eugene ZCommented:
create cursore
and use to get db names in @variable:
select name from master.dbo.sysdatabases

also you can check:
http://www.sqlservercentral.com/scripts/contributions/468.asp
0
 
ThorinAuthor Commented:
I am sorry...I am not super experienced with Cursors.  Can you provide more details on this solution?  Thanks.
0
 
Eugene ZCommented:
try:
/from http://www.sqlservercentral.com/scripts/contributions/847.asp/

CREATE PROCEDURE GlobalBackup
as

Set NoCount On

Declare
      @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'

Declare
      @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
      Begin
            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
                  Begin
                        Set @vCount = @vNumDBs
                        Set @vErrorString = 'net send * SQL Backup failed on (' + @vDBName + ')! Please view event log!'
                        Exec XP_CMDShell @vErrorString
                  End
            Set @vCount = @vCount + 1
      End



0
 
ThorinAuthor Commented:
Wahooo!  Thank you very much, that worked perfectly!  Once again, Experts Exchange helps to make me look good!  THANK YOU!
0
 
Eugene ZCommented:
you are always welcome!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now