Full database backup for SQL 2000 - Daily Backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Full database backup for SQL 2000 - Daily Backup

Author

Topic

kakikupart
Starting Member

11 Posts

Posted - 2011-05-24 : 23:12:51

Hi all,

Anyone know,
1. How to backup 2 database on the same script for example I have two database TESTA and TESTB on the same server.
2. How to add delete old backup files that are older than 7 days.
3. How to add copy backup files at 2 backup directory example drive C and Z.
I have this script from the net but don't know how to do.Really appreciate your help

declare @backupFileName varchar(100),
@backupDirectory varchar(100),
@databaseDataFilename varchar(100),
@databaseLogFilename varchar(100),
@databaseDataFile varchar(100),
@databaseLogFile varchar(100),
@databaseName varchar(100),
@execSql varchar(1000)
Set the name of the database to backup
set @databaseName = 'TESTA'
Set the path fo the backup directory on the sql server pc
set @backupDirectory = 'c:\temp\'
Create the backup file name based on the backup directory, the database name and today's date
set @backupFileName = @backupDirectory + @databaseName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'
select @databaseDataFile = rtrim([Name]),
@databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
inner join
master.dbo.sysfilegroups as groups
on
files.groupID = groups.groupID
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName )
select @databaseLogFile = rtrim([Name]),
@databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName )
and
groupID = 0
print 'Backing up "' + @databaseName + '" database to "' + @backupFileName + '" with '
print ' data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'
print ' log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'
set @execSql = '
backup database [' + @databaseName + ']
to disk = ''' + @backupFileName + '''
with
noformat,
noinit,
name = ''' + @databaseName + ' backup'',
norewind,
nounload,
skip'
exec(@execSql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources