Re: Backu plan
From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 11/19/04
- Next message: Joe Celko: "Re: Querying for parent records whose child records all have the same value."
- Previous message: Imtiaz: "Re: Importing Chinese characters using DTS BulkInsert"
- In reply to: Ed: "Re: Backu plan"
- Next in thread: Andrew J. Kelly: "Re: Backu plan"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 19 Nov 2004 15:53:48 -0500
You can write a small script to delete files older than X...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ed" <Ed@discussions.microsoft.com> wrote in message
news:54D2703C-845E-48BC-BCDB-3C98AAE220BD@microsoft.com...
> Thanks...
> but if i use the codes, the folder will have so many backup files after
> days/months, does that mean i have manually delete all unused backups???
> Thanks again...
> Ed
>
> "Andrew J. Kelly" wrote:
>
> > Why not simply use a different file name for each backup. Here is a
simple
> > example of backing up all DB's to disk and it will generate a different
file
> > each day based on the datetime:
> >
> >
> > 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
> >
> >
> > "Ed" <Ed@discussions.microsoft.com> wrote in message
> > news:B715BC32-C9B5-436D-B469-E0866FBF1756@microsoft.com...
> > >I would like to know the proper backup plan
> > > the previous company that I worked, they backup all the database into
two
> > > folders with the same drive...
> > >
> > > Z:\SQL\Backup\Users
> > > Z:\SQL\Backup\Systems
> > >
> > > In each folder, they also contain five different folders called
"Monday",
> > > "Tuesday", .... to "Friday"
> > >
> > > The reason why they do that is because they don't want to keep
appending
> > > the
> > > backup to the prior one...
> > >
> > > If i follow the above rules, do i need to create five different backup
> > > schedule since they are in different folders?
> > >
> > > Please suggest.
> > >
> > > Thanks
> > >
> > > Ed
> >
> >
> >
- Next message: Joe Celko: "Re: Querying for parent records whose child records all have the same value."
- Previous message: Imtiaz: "Re: Importing Chinese characters using DTS BulkInsert"
- In reply to: Ed: "Re: Backu plan"
- Next in thread: Andrew J. Kelly: "Re: Backu plan"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
