May 24, 2011 at 9:08 pm
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)
May 25, 2011 at 9:50 am
Your scenario is a perfect example as to why a Maintenance Plan would be used. It allows you to select one, multiple, or all databases (system dbs included) in one simple task. Maintenance Plans also make the "cleanup task" one of the most simple steps in the whole process. Unless you are married to a script, I highly recommend you take a look at creating a Maintenance Plan.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
May 25, 2011 at 6:48 pm
Thanks Chris,it because maintenance plans can't be run manually or execute manually by users.Is there any way to do that ....
May 25, 2011 at 6:56 pm
ridzuan_first (5/25/2011)
Thanks Chris,it because maintenance plans can't be run manually or execute manually by users.Is there any way to do that ....
Maintenance plan is supposed to create a job. That job can be started manually in EM if you have the right permissions... but normal users shouldn't have access to that, only DBA (or responsible person in that matter).
May 25, 2011 at 8:34 pm
Thanks,but need a shortcut on the server desktop to ease my users execute it.Is there any way to generate all script from maintenance plan?because plan to deploy 100 over server....
May 26, 2011 at 5:36 am
ridzuan_first (5/25/2011)
Thanks,but need a shortcut on the server desktop to ease my users execute it.Is there any way to generate all script from maintenance plan?because plan to deploy 100 over server....
I've never done that on sql 2000. I know powershell was invented to solve that problem but I don't know if it works on sql 2k.
But the REAL point is that you should NEVER give that access / responsibility to normal users. So PLZ stay away from that option.
June 2, 2011 at 3:34 am
My only thing I need to do is delete the old database files older than 7 days.
June 2, 2011 at 5:46 am
ridzuan_first (6/2/2011)
My only thing I need to do is delete the old database files older than 7 days.
Search this site for scripts... there are plenty that do that. Mine is proprietary so it's no use outside our company.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply