Re: SQL Server backup within SQL Server Management Studio
- From: John <John@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 4 Dec 2009 16:00:01 -0800
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:
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:18409CAA-9C65-4A52-95EE-5CF561B7B789@xxxxxxxxxxxxxxxx
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
.
- Prev by Date: Re: SQL Server 2008 R2 Installation Issue
- Next by Date: SQL 2008 + TServer on same box Virtual or Not?
- Previous by thread: sqlservr.exe process memory
- Next by thread: SQL 2008 + TServer on same box Virtual or Not?
- Index(es):
Relevant Pages
|
