June 3, 2005 at 8:37 am
Is it possible to create a SQL Server 2000 Job on Server "A" which restores a database on Server "B"?
The following syntax does NOT include a SERVER Name:
restore database DB_1 from disk='\\SERVER_C\DB_1.bak'
with recovery, replace,
move 'DB_1_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_1_Data.MDF',
move 'DB _1_Log' to 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\DB_1_Log.LDF'
June 3, 2005 at 8:50 am
The restore has to be run from the server that is restoreing the database. So I'd say No based on your descrioption.
If you mean restore a database on Server A whose backup file is stored on Server B, then that can be done if the account running SQLAgent has network access to the fiels. I don't recommed this because any network hiccup will cause the job to fail. You should really copy the files locally before restoring.
June 3, 2005 at 11:11 am
You can do this via DTS.
I had a package (on Server B) that initiated a backup on a database to a backup device located on a network share. You need to have this backup device configured on both servers (pointing to the same file). It won't matter which server the package executes from, as long as the connections are correctly set up.
Anyway, here's how it goes:
1. Execute SQL Script (connection - Server A). Backup database script (to backup device listed above).
On Success:
2. Execute SQL Script (connection - Server B). Restore database script (from backup device listed above).
I also had to use a script to kill all users of the destination database (i found on on this site), as well as fix the orphaned users (ditto).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply