Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL Restore to a different Server with Diffs

Posted on 2014-04-15
6
Medium Priority
?
132 Views
Last Modified: 2014-04-15
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
0
Comment
Question by:johnojohno
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40001484
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
 

Author Comment

by:johnojohno
ID: 40001512
Hi Shaun,

I need the move statements as I'm restoring them on a different server.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40001517
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:johnojohno
ID: 40001522
When I place the norecovery option it errors on the Move.
Incorrect syntax near 'MOVE'
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 400 total points
ID: 40001527
You need a comma after NORECOVERY. Each WITH clause needs to be comma separated.
0
 

Author Comment

by:johnojohno
ID: 40001636
Carl many thanks buddy! That worked like a charm. :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question