restoring db to different sql machine

[cippirimerlo]

hi everybody,
this is my problem:
(os is w2k adv. server on all machines)
I have 1 SQL 2000 (Enterprise) server with legato networker for sql server (db is backup on tape, every night, 1 full 6 level 1, about 45 GB each);
I need to schedule automatic restore (from tape) to another SQL 2000 server machine (with no legato sql module)

My first problem is that using the sql networker client in the restore dialog, I can restore files only on local drives

anyone knows if it's possible to use (and how) nsrsqlrc to do this job?
thanks

 

[SchuhTL]

I think you can schedule a job on the destination sql server and execute the 1st part of the syntax below. The 2nd part is what actually gets sent to sql server. You will also need to make sure you kill all users in the db that is being restored prior to the restore. I

--syntax to restore full backup with different name to another server (must have destination server set up as a client of the source server)
-- -s = NetWorker server name
-- -c = Client Name (source)
-- -f = equivalent of t-sql "Replace"
-- -d MSSQL: = Name of destination database (can be the same as source if needed)
-- -C = location of files
nsrsqlrc -s NetworkerServerNameGoesHere -c ClientServerNameGoesHere -f -d MSSQL:Test2 MSSQL:Test1 -C "'Test_Data'='C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test2_Data.MDF', 'Test_Log'='C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test2_Log.LDF'"

--resulting sql statement sent to the sql server
--RESTORE database [Test2] FROM virtual_device='Legato#b3fa2629-4896-42f4-88e28658bfc3f57b' WITH move 'Test_Data' --to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test2_Data_0.MDF', move 'Test_Log' to 'C:\Program --Files\Microsoft SQL Server\MSSQL\Data\Test2_Log_0.LDF', replace