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 @ServerNamevarchar(400)
DECLARE @DatabaseNamevarchar(400)
DECLARE @BackupPathvarchar(400)
DECLARE @TSQLCommandvarchar(2000)
DECLARE @FullBackupJobvarchar(2000)
DECLARE @DiffBackupJobvarchar(2000)
DECLARE @BackupDevicevarchar(2000)
DECLARE @FullStepNamevarchar(2000)
DECLARE @DiffStepNamevarchar(2000)
DECLARE @FullSchNamevarchar(2000)
DECLARE @DiffSchNamevarchar(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