Trying to use a sql statement to backup 50 databases in a SQL instance. Want
to create a backup device for each database to write the full and also append
its T-logs to the device. The device would be labeled 'dbname1-day of the
week'. The backups are to write to a new device each day until it reaches
the 8th day. At which point it will overwrite the existing backup device
file. My issue is I tried to run it as a maintenance job, but it only backs
up the master. How can make it backup all databases.

Thanks in advance


declare @dbName varchar(100),
@deviceName varchar(100),
@devicePath varchar(100),
@descInfo varchar(100),
@id integer

-- change this to the physical path where the backup devices should be stored
select @devicePath = 'g:\sqlbackup'

select @descInfo = '[' + db_name() + '] full backup'
select @dbName = db_name()
select @deviceName = db_name() + convert( char(1), datepart( dw,
getdate() )) + '-' + datename( dw, getdate() )

select @devicePath = @devicePath + '\' + @deviceName + '.bak'

if not exists (select * from master.dbo.sysdevices where name = @deviceName)
begin
EXEC sp_addumpdevice 'disk', @deviceName, @devicePath
end

BACKUP DATABASE @dbName TO @deviceName WITH INIT, NOUNLOAD, NAME =
@descInfo, STATS = 10, NOFORMAT

John,

You need to use a cursor to loop thru each database similar to the example
below. But you definitely don't want to use dump devices and append as you
mentioned. For one that makes it very inflexible in that you can not delete
an individual backup, it's all or nothing. You really should look at backing
up to individual files instead. Another option would be to just use the
maintenance plans as they will do all of this for you and even delete the
older backups as well.

DECLARE @DBName NVARCHAR(100), @Device NVARCHAR(100), @Name NVARCHAR(150)


DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
-- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')


OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Device = N'C:\Data\Backups\DD_' + @DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'

SET @Name = @DBName + N' Full Backup'

PRINT 'Backing up database ' + @DBName

BACKUP DATABASE @DBName TO DISK = @Device WITH INIT , NOUNLOAD ,
NAME = @Name, NOSKIP , STATS = 10, NOFORMAT

RESTORE VERIFYONLY FROM DISK = @Device WITH FILE = 1

PRINT '--------------------------------------- '

FETCH NEXT FROM cur_DBs INTO @DBName
END

CLOSE cur_DBs
DEALLOCATE cur_DBs




--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"John" <John (AT) discussions (DOT) microsoft.com> wrote

Thanks Andrew,

After thinking it through I setup a maintenance plan and it is working as
expected. I have over 70 databases in this one instance and the bak and trn
files are numerous, but at least I have the flexibility of moving around
these smaller individual database backup files rather than one huge device
file.

Thanks again,

"Andrew J. Kelly" wrote: