[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to restore sql 2005 database on sql 2008 server from mdf when older copy of database already on new server

Posted on 2016-11-01
5
Medium Priority
?
123 Views
Last Modified: 2016-11-01
I have a SQL Server 2005 Express database on a Windows 2003 server which recently crashed.  I had been working on a new server using Server 2008 and SQL 2008 R2, but the project had been sidelined due to more pressing matters.  I wasn't able to get SQL Server Management Studio Express to open on the old server, but I did locate the mdf and ldf files for the database and copied them to a flash drive.  I then copied the files into the SQL data location on the new database, though Windows balked since there's already a database with that name.  I did make a backup of the database on the new server, which I copied off of the server, before this process.  The question is, since a SQL database with the same name already exists on the new server, how can I import the new data into the existing database?  I searched on the issue of restoring databases using only mdf and ldf files, and apparently it is possible, though I'm getting errors (see attached Word document with screenshots).

Thanks in advance,
Michael
0
Comment
Question by:mbrayc
[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
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41868434
First thing first, download the SQL Management Studio 2016, which is completely free now. With that you will be able to connect to any server:

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Second, you don't have anything attached.

Third, it is a bit confusing what you say. Do you have the database files, .mdf and .ldf, or a backup file from your database?

If you have the database files you can attach them to a new server but not if there is already a database with the same name on it. If you still want to attach it you will have to rename the existing database before you do that. If the database file names are the same with those of the existing database then you will have to put them in a different folder before you attach them.

If you have a backup file than you can restore it into a new database with a new name, just have to specify that in the restore statement. If the file names are the same, again you will have to use a different folder.
0
 

Author Comment

by:mbrayc
ID: 41868475
Thanks for the input.  I will get the 2016 studio the first chance I get.  I was able to get the old server restarted, repair sql 2005, then run a fresh backup from the old server and restore to the new.  I checked the data and it looks good so far.  Now I need to run some additional testing to verify this, but for now it looks like I dodged a bullet.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41868490
Make sure you always have a maintenance backup plan in place with backup and data integrity check. I recommend you to use these stored procedures and solution that are completely free:

https://ola.hallengren.com/
0
 

Author Closing Comment

by:mbrayc
ID: 41868518
I will definitely do that.  Normally we do a full backup of the failed server several times a week through Symantec backup exec.  Unforunately, in this case, the last backup that backup exec would recognize was several years ago (yes I said years).  I will definitely revise my backup plans with this in mind, to include periodic test restores of those backups (which wasn't part of the current plan).
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41868610
In the SQL world you should never use the utility operating system backup tools only! You HAVE to use the SQL native backup features, which will ensure you how close to the present can the data be recovered in the case of failure. Several times a week means that in between the last valid backup and the moment of possible failure could be a distance of days, which in most cases it is not even remotely close enough. You need to implement a plan with weekly full backups, daily deferential backups and continuous log backup at an interval of your convenience which can range from 1 minutes to several minutes, an hour or more. The most frequent they are the better because the less data you will lose.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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