MS SQL Restore to a different Server with Diffs

Hi Experts,
I'm creating a script to drop a database and restore with the latest diff file, but I'm having difficulty with the diff. I can manage to drop and restore the database with the full backup. See below at the code.

USE master
--GO
--ALTER DATABASE Live1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--DROP DATABASE Live1;
--RESTORE DATABASE Live1 FROM DISK='C:\Users\test\Desktop\Full Database.bak'  
--WITH NORECOVERY
--MOVE 'MDF' TO 'C:\testdata\Test.mdf',
--MOVE 'Log' TO 'C:\testdata\Test_log.ldf',
--MOVE 'FullText' TO 'C:\testdata\Test_log.ndf'
GO
--RESTORE DATABASE Live1 FROM DISK='C:\Users\test\Desktop\DIFF.bak'
--WITH RECOVERY
--MOVE 'Data' TO 'C:\testdata\Test.mdf',
--MOVE 'Log' TO 'C:\testdata\Test_log.ldf',
--MOVE 'FullText' TO 'C:\testdata\Test_log.ndf'

Thanks,
J
johnojohnoAsked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
You need a comma after NORECOVERY. Each WITH clause needs to be comma separated.
0
 
Shaun KlineLead Software EngineerCommented:
Just a guess, based on reading MS website on differential restores, but I don't believe you need the MOVE statements. However, are you receiving an error? If so, what is it?
0
 
johnojohnoAuthor Commented:
Hi Shaun,

I need the move statements as I'm restoring them on a different server.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Carl TawnSystems and Integration DeveloperCommented:
As stated by Shaun Kline, you don't need the MOVE statements for the Diff as you are restoring to an existing database. The MOVE statements in the database restore are what will physically relocate the files.

What is the problem you are currently having?
0
 
johnojohnoAuthor Commented:
When I place the norecovery option it errors on the Move.
Incorrect syntax near 'MOVE'
0
 
johnojohnoAuthor Commented:
Carl many thanks buddy! That worked like a charm. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.