Jump to content
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

Sign in to follow this  
Programador

SQL Job

Recommended Posts

Programador

Ola malta

Os backups da minha base de dados tem de ser atraves de sql jobs.

use [msdb]
go
Declare @jobId BINARY(16)
--
Declare @vchname varchar(50)
Declare @vchpath varchar (100)
Declare @vchfilename varchar(100)
Declare @vchfiledate varchar(20)

Set @vchpath = 'C:\users\Administrator\Desktop\Backup\'
Select @vchfiledate = CONVERT(varchar(20),getdate(),112)
Set @vchname = 'InventardatenbankVollständig'
Set @vchfilename = @vchpath + @vchname + '_' + @vchfiledate + '.bak'

--

Exec  msdb.dbo.sp_add_job @job_name=N'Datenbank sichern - Inventardatenbank', 
	@enabled=1, 
	@notify_level_eventlog=0, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[uncategorized (Local)]', 
	@owner_login_name=N'WIN-N6WMYS4OBDH\Administrator', @job_id = @jobId OUTPUT
select @jobId
go
Exec msdb.dbo.sp_add_jobserver @job_name=N'Datenbank sichern - Inventardatenbank', @server_name = N'WIN-N6WMYS4OBDH'
go
use [msdb]
go
Exec msdb.dbo.sp_add_jobstep @job_name=N'Datenbank sichern - Inventardatenbank', @step_name=N'1', 
	@step_id=1, 
	@cmdExec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command='BACKUP DATABASE [inventardatenbank] TO  DISK = @vchfilename WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
go
', 
	@database_name=N'master', 
	@flags=0
go
use [msdb]
go
Exec msdb.dbo.sp_update_job @job_name=N'Datenbank sichern - Inventardatenbank', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=0, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[uncategorized (Local)]', 
	@owner_login_name=N'WIN-N6WMYS4OBDH\Administrator', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
go
use [msdb]
go
Declare @schedule_id int
Exec msdb.dbo.sp_add_jobschedule @job_name=N'Datenbank sichern - Inventardatenbank', @name=N'SQL Backup', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20100315, 
	@active_end_date=99991231, 
	@active_start_time=0, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
go

Na linha, aonde esta @command, gostava de utilisar a variavel "@vchfilename", mas nao funciona. A variavel "vchfilename " contem o nome da base de dados e a data actual. O meu objectivo e utilisar no sql job uma variavel, na qual consigo configurar o nome do ficheiro que contem o backup da base de dados.

Desde ja agradeco a vossa ajuda

Cumprimentos

Programador

Share this post


Link to post
Share on other sites
M6

Creio que não podes passar argumentos a um JOB, podes é ter isso num SP que recebe os argumentos e fazes a chamada com argumentos a partir do JOB.


10 REM Generation 48K!
20 INPUT "URL:", A$
30 IF A$(1 TO 4) = "HTTP" THEN PRINT "400 Bad Request": GOTO 50
40 PRINT "404 Not Found"
50 PRINT "./M6 @ Portugal a Programar."

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.