Solved

Updating the database from SQL Server 2008 to 2012

Posted on 2012-12-29
11
590 Views
Last Modified: 2012-12-29
I have been using SQL Server 2008 With Visual Studio 2008. Now I am using SQL Server 2012 With Visual Studio 2012. For now, I just want to get the database updated so I can restore it in SQL Server 2012. Is there a query that will handle this task? I tried using the restore query that I always use:
Database name:  --          jandmmfg

Restore database jandmmfg from disk
= ‘F:\Backups\SQL\jandmmfg.BAK’
With recovery, replace

...

This gave me an error…

...

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'jandmmfg' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'jandmmfg_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
Comment
Question by:jampost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38730140
I have seen this error when restoring to a new database and the destination directory doesn't exist.  SQL Server won't create directories for you.

Does the directory "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA" exist ?
0
 

Author Comment

by:jampost
ID: 38730143
sjwales,

That directory exists in SQL Server 2008, but not in 2012.
0
 

Author Comment

by:jampost
ID: 38730146
SQL Server 2008 was Developer edition. I am using SQL Server 2012 Express edition while I wait for the delivery of SQL Server 2012 Developer edition if that makes a difference.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 38730164
Use the MOVE clause to place the data and log files wherever you want.  A word of caution, typically using C: is not a good choice, but if this is just your home version it should be fine.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 38730166
Create the directories then, and try again.

The original error message even says: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\jandmmfg_log.ldf" failed

Create the destination directory, try again and you may want to move the files around afterwards to where ever you need them to be.

In fact, there was a question asked today by someone else about moving DB files around:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27980580.html
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38730167
Something like this (change the logical names and enter the full path to the data files including their physical names as appropriate):
RESTORE DATABASE jandmmfg
   FROM DISK= 'F:\Backups\SQL\jandmmfg.BAK'
   WITH NORECOVERY, 
      MOVE 'jandmmfg_Data' TO '<full path of your data file goes here>', 
      MOVE 'jandmmfg_Log' TO '<full path of your transaction log file goes here>'

Open in new window

0
 

Author Comment

by:jampost
ID: 38730172
acperkins,

I received this error after the MOVE statement.


Msg 3234, Level 16, State 2, Line 1
Logical file 'jandmmfg_Data' is not part of database 'jandmmfg'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 

Author Comment

by:jampost
ID: 38730173
sjwales

I might try that to get things rolling.
0
 

Author Comment

by:jampost
ID: 38730180
sjwales,

    Creating the folders did not work.
0
 

Author Closing Comment

by:jampost
ID: 38730199
Thank you all for your help.  I am going do an upgrade in place from SQL Server 2008 to 2012. I think that in the ticket. Happy New Year everybody.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38730201
Logical file 'jandmmfg_Data' is not part of database 'jandmmfg'.
That is why I specifically stated change the logical names and enter the full path to the data files including their physical names as appropriate.  I have no idea what names you used.  And yes using RESTORE FILELISTONLY will answer that question for you.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

751 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