schedule a restore as a job 1

[ktucci]

what is the best way to schedule a restore to happen automatically...

thanks

keith

 

[tlbroadbent]

My preferences -

[ol][li]Create a stored procedure to perfrom the restore. Schedule a Job to execute the SP.
[li]Create and schedule a Job to execute T-SQL code that does the restore.[ol]

You could also use a Windows scheduler to start a batch Job that executes OSQL or ISQL. These utility programs could execute a SP or T-SQL code. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[ktucci]

what is the syntax for performing a restore from a stored procedure...i have been using the restore wizard up until this point

keith

 

[tlbroadbent]

The simple form of a T-SQL restore is

RESTORE DATABASE MyNwind FROM MyNwind_1

where MyNwind_1 is the name of the backup device.

Here is the complete syntax from SQL Books Online (SQL Server 7.0). See the top "RESTORE (T-SQL)" in BOL.

Restore an entire database:

RESTORE DATABASE {database_name | @database_name_var}
[FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] MOVE 'logical_file_name' TO 'operating_system_file_name']
[,...n]
[[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE]
[[,] RESTART]
[[,] STATS [= percentage]]
]

Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[ktucci]

thanks a million...that answers my question