September 25, 2002 at 8:00 am
Hello to all. A beginners question, I think.
I have been restoring db's using, either EM or t-sql. I have a question, though.
Latelly, we are runnig out of space in one of the servers where we run a few DTS jobs. I am planning to move these DTS jobs from the production system to a new DTS dedicated machine (not running anything else but DTS jobs, which are quite a few and use a lot of resources from the production system.)
These DTS move data from client system into SQL Server db's. The production system will continue to run the production db's.
We will run all DTS jobs in the dedicated machine and I need to restore those db's after the DTS are completed. Of course it is not a one time only thing, but a daily restore or attach these db's to the production server.
Can anyone give some ideas in how to schedule restore db's. I played yesterday with a sample db and t-sql works as well as EM, but I cannot figure out how to schedule these jobs. All your help is always welcome.
September 25, 2002 at 8:13 am
You can schedule whatever you want using a SQL Server Agent job. If you look in EM there is a directory called "Management" under that there is another directory called "SQL Server Agent". Under the "SQL Server Agent" there are "Jobs". Job's can be build to do about anything, as well as be scheduled. Of course to use Jobs you will also need to start SQL Server Agent.
Hope this helps.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 25, 2002 at 8:18 am
Thank you Greg. I know that. I run quite a few jobs and schedule them to a certain time and everything is working fine. Sorry if I did not explain myself correctly.
What I need is not ro schedule any job, but the restore process, it has to restore the db.s from the dedicated machine to the production server. Thank you for your input anyway.
September 25, 2002 at 8:37 am
You might consider using a linked server from your dedicated machine to your production machine. This way from your dedicated machine you can launch jobs on your production machine using the sp_start_job, or sp_add_jobschedule. Your production machine will also need access to your dedicated machines backups. You should be able to create a backup device that points to a network drive, so both your SQL boxes can see the same backup.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 25, 2002 at 8:48 am
Thanks again Greg. Again, what I need is the way I can create the schedule using T-SQL.
Like:
RESTORE DATABASE MyDB
FROM MyDB_BAK
@active_start_date = 20020805, @active_start_time = 0, @freq_interval = 10, @freq_subday_type = 1
This is just a example taken from other job I created. This one only do backup, not RESORE. I have not figure out how to schedule the restore. All other restore actions are OK and I basically know how to, but way to schedule the restore. Thank you
September 25, 2002 at 9:06 am
If I understand you correctly you want some code that will schedule a job. Here is some code that will schedule job "ADRN1303 TRIGGER" to run in 15 minutes for the current time. Of course "ADRN1303 TRIGGER" already needs to exist prior to running this code. Is this what you are looking for?
declare @seconds nvarchar(2)
declare @interval_amt nvarchar(3)
-- Set @interval to the interval you want to add to the current date
set @interval = 'mi'
-- Set @interval_amt number of intervals you would like to add
set @interval_amt = '15'
set @query = 'set select @rtn=datepart(yyyy,dateadd(' + @interval + ',' + @interval_amt + ',getdate()))'
exec sp_executesql @query, N'@rtn nvarchar(4) output', @year output
set @query = 'set select @rtn=datepart(mm,dateadd(' + @interval + ',' + @interval_amt + ',getdate()))'
exec sp_executesql @query, N'@rtn nvarchar(4) output', @month output
set @query = 'set select @rtn=datepart(dd,dateadd(' + @interval + ',' + @interval_amt + ',getdate()))'
exec sp_executesql @query, N'@rtn nvarchar(4) output', @day output
if len(@month)= '1'
begin
set @month = '0' + @month
end
if len(@day)= '1'
begin
set @day = '0' + @day
end
set @query = 'set select @rtn=datepart(hh,dateadd(' + @interval + ',' + @interval_amt + ',getdate()))'
exec sp_executesql @query, N'@rtn nvarchar(4) output', @hours output
if len(@hours)= '1'
begin
set @hours = '0' + @hours
end
set @query = 'set select @rtn=datepart(mi,dateadd(' + @interval + ',' + @interval_amt + ',getdate()))'
exec sp_executesql @query, N'@rtn nvarchar(4) output', @minutes output
if len(@minutes)= '1'
begin
set @minutes = '0' + @minutes
end
set @query = 'set select @rtn=datepart(ss,dateadd(' + @interval + ',' + @interval_amt + ',getdate()))'
exec sp_executesql @query, N'@rtn nvarchar(4) output', @seconds output
if len(@seconds)= '1'
begin
set @seconds = '0' + @seconds
end
set @query = 'msdb.dbo.sp_add_jobschedule @job_name="ADRN1303 TRIGGER", @active_start_date = ' +
@year + @month + @day +
',@active_start_time=' + @hours + @minutes + @seconds +
',@name="restart",@freq_type=1, @enabled=1'
exec sp_executesql @query
print @query
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 26, 2002 at 5:29 am
If you are looking for a way to fully automate both the backup of the primary server, and to then restore the secondary server, you may want to take a look at the SQL Server 2000 Resource Kit section called "simple log shipper"
The SLS has two stored procedures that backup and then restore the database logs. You can modify these procedures to do a full backup on the primary, rather than the logs.
It also is a good, economical way to log ship without buying the Enterprise version.
Hope this helps.
What's the business problem you're trying to solve?
September 26, 2002 at 5:32 am
Thank you. Log shipping was one option I couldn't use because we do not have Enterprise. Thank you very much. That will help a lot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply