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
62 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

708 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

17 Experts available now in Live!

Get 1:1 Help Now