Backup script

Hello Friends,
I am looking for a script to automate my sql2000 databases backup. Currently
I am able to backup all databases but next backup will override my last
backup. Is there a script I can use which backup a database and then asign it
a new file name to it so I can keep backup of all databases for a week. Also
is it possible to create a job to delete all backups which are older than one
week?
I thank you for your help...
Regards,
Jamil
I would think the easiest way to do this is using Maintenance Plans from
Enterprise Manager.
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamil Ahsan" <JamilAhsan@.discussions.microsoft.com> wrote in message
news:74EE2CC0-DF32-47D2-8D92-C78CE88A93D8@.microsoft.com...
> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup.
Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign
it
> a new file name to it so I can keep backup of all databases for a week.
Also
> is it possible to create a job to delete all backups which are older than
one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||The following scripts will backup the database and name the backup along with
the date...
try to create the following script:
After executing the following scripts, you may add another VB script to
delete any files more than 7 days old
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')
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
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil
|||http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
This allows you to specify how long you want the backup files to be retained.
Also make sure yyou test the backups - this wil do it automatically
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
http://www.mindsdoor.net/SQLAdmin/s_...estBackup.html
"Jamil Ahsan" wrote:

> Hello Friends,
> I am looking for a script to automate my sql2000 databases backup. Currently
> I am able to backup all databases but next backup will override my last
> backup. Is there a script I can use which backup a database and then asign it
> a new file name to it so I can keep backup of all databases for a week. Also
> is it possible to create a job to delete all backups which are older than one
> week?
> I thank you for your help...
> Regards,
> Jamil