July 21, 2004 at 4:46 am
hi,
I have the script to take SQL databases backup as below but i wish to run it using a batch file and then use the schedule task to schedule the backup.
I am using the MSDE2000 engine.
I have tried using the script at the osql prompt and it works.
Use master
go
declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
while @IDENT is not null
begin
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''d:\backup\jaison\'+@DBNAME+'.BAK''WITH INIT'
PRINT @SQL
EXEC (@SQL)
select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
end
Thanx,
Jaison lucas.
July 21, 2004 at 9:30 pm
Put this into a file called backup.sql for example. In your batch file, call osql with the -i backup.sql syntax. You'll need to run it in master with an account that's db or backup administrator on the server.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
September 23, 2006 at 10:55 pm
use this script to make a backup strategy
--
use master
go
DECLARE @ServerName varchar(400)
DECLARE @DatabaseName varchar(400)
DECLARE @BackupPath varchar(400)
DECLARE @TSQLCommand varchar(2000)
DECLARE @FullBackupJob varchar(2000)
DECLARE @DiffBackupJob varchar(2000)
DECLARE @BackupDevice varchar(2000)
DECLARE @FullStepName varchar(2000)
DECLARE @DiffStepName varchar(2000)
DECLARE @FullSchName varchar(2000)
DECLARE @DiffSchName varchar(2000)
SET @ServerName = HOST_NAME()
SET @DatabaseName = 'Database name'
SET @BackupPath = 'Unit:\Complete path\' + @DatabaseName + '.bk!'
SET @FullBackupJob = @DatabaseName + '_Weekly_Full_Backup'
SET @DiffBackupJob = @DatabaseName + '_Daily_Diff_Backup'
SET @BackupDevice = @DatabaseName + '_Backup'
SET @FullStepName = @DatabaseName + '_Full_Backup'
SET @FullSchName = 'Schedule_' + @DatabaseName + '_Weekly_Full_Backup'
SET @DiffSchName = 'Schedule_' + @DatabaseName + '_Daily_Diff_Backup'
SET @DiffStepName = @DatabaseName + '_Diff_Backup'
-- Delete the device if exists
IF EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = @BackupDevice)
BEGIN
EXEC sp_dropdevice @BackupDevice
END
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @FullBackupJob)
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @FullBackupJob
END
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @DiffBackupJob)
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @DiffBackupJob
END
EXEC sp_addumpdevice 'disk', @BackupDevice, @BackupPath
EXEC msdb.dbo.sp_add_job @job_name = @FullBackupJob,
@owner_login_name = 'sa' -- Creates the job of Full Backup
EXEC msdb.dbo.sp_add_jobserver @job_name = @FullBackupJob,
@server_name = @ServerName
SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH INIT , NOUNLOAD , NAME = N''' + @DatabaseName + '_Weekly_Full_Backup'', NOSKIP , STATS = 10, NOFORMAT'
EXEC msdb.dbo.sp_add_jobstep @job_name = @FullBackupJob,
@step_name = @FullStepName,
@subsystem = 'TSQL',
@command = @TSQLCommand,
@retry_attempts = 5,
@retry_interval = 5
EXEC msdb.dbo.sp_add_jobschedule @job_name = @FullBackupJob,
@name = @FullSchName, -- Creates the Job Schedule
@freq_type = 8, -- Weekly
@freq_interval = 2, -- Monday
@freq_recurrence_factor = 1, -- Every week
@active_start_time = 083000 -- HHMMSS (08:30:00 AM)
EXEC msdb.dbo.sp_add_job @job_name = @DiffBackupJob,
@owner_login_name = 'sa' -- Creates the job of Differencial Backup
EXEC msdb.dbo.sp_add_jobserver @job_name = @DiffBackupJob,
@server_name = @ServerName
EXEC msdb.dbo.sp_add_jobschedule @job_name = @DiffBackupJob,
@name = @DiffSchName, -- Creates the Job Schedule
@freq_type = 8, -- Weekly
@freq_interval = 60, -- Tuesday, Wednesday, Thursday and Friday
@freq_recurrence_factor = 1, -- Every week
@active_start_time = 083000 -- HHMMSS (08:30:00 AM)
SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N''' + @DatabaseName + '_Daily_Diff_Backup'', NOSKIP , STATS = 10, NOFORMAT '
EXEC msdb.dbo.sp_add_jobstep @job_name = @DiffBackupJob,
@step_name = @DiffStepName,
@subsystem = 'TSQL',
@command = @TSQLCommand,
@retry_attempts = 5,
@retry_interval = 5
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply