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
108 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 500 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

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

687 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