0

I have 2 databases .mdf SQL Server 2000, and I need to restore them on SQL Server 2014. After some research, I saw that the best approach is making a .bak of this 2 files.

Well, I need to install SQL Server 2000 to do this. I tried some youtube tutorial videos but without results.

Can you show me a way to do this?

  • 1
    Dear God, get yourself a real backup of the databases immediately. A million things can go wrong with the MDF file. – Ali Razeghi May 2 '16 at 19:27
  • 1
    @AliRazeghi probably the files are corrupted, I do not know. They sent me this to make tutorials with OBIEE. I do not think that this has important stuff. – Wiz May 3 '16 at 9:06
  • Not too surprising that an ancient mdf/ldf might be corrupted, as Ali Razeghi called out. – RLF May 3 '16 at 17:56
3

Here is a process that you need to go through for upgrading from SQL Server 2000 to SQL Server 2014.

http://sqlmag.com/sql-server-2014/sql-select-steps-migrate-sql-server-2000-sql-server-2014

You will see that you need to

  1. Restore a SQL Server 2000 backup to SQL Server 2008 as one step, followed by
  2. Restore of the (now upgraded) 2008 version to SQL Server 2014.

If you only have .mdf files (not a full backup of the database) then you will need to create .ldf (log) files for the instance. This is not a recommended method for restores, but may be what you need. Perhaps use marc_s script:

CREATE DATABASE Test_Data
ON PRIMARY 
(FILENAME = N'D:\Test_Data\Test_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO 

Or, there are several posts out there about attaching a database without a transaction log file. One random choice:

http://www.mytechmantra.com/LearnSQLServer/How-to-attach-database-without-a-transaction-log-file-in-SQL-Server.html

If your SQL Server 2000 .mdf was from SQL Server 2000 SP4 then you may be able to attach it to a SQL Server 2008 server. If the file has been corrupted, of course, then there is little to be done.

From this location you can apparently download the MSDE engine and SP 4. The MSDE engine will only handle a 2GB database.

https://msdn.microsoft.com/en-us/sqlserver/bb895925

  1. Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A
  2. Microsoft SQL Server 2000 Service Pack 4

MSDE has many limitations, including only 2GB databases. (And it has been so long since I used it that I do not remember all the problems and limitations.) See:

https://technet.microsoft.com/en-us/library/ms165672(v=sql.90).aspx

If you need a full version of SQL Server 2000, then I would suggest that you talk to other SQL Server users in the area and find one who still has retained an original install disk.

There are websites that say they have the media, but I cannot recommend them.

  • I have the logs too. In my company, I am the only one with a bit knowledge on DBA... And I just graduate 1 month ago. I am creating a VM with XP, to see if this process is easier than W10. – Wiz May 2 '16 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.