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
84 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
  • 3
  • 2
5 Comments
 
LVL 26

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 26

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 26

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now