Moving mdf, ndf and ldf files to another SQL Server2000

Question

Moving mdf, ndf and ldf files to another SQL Server2000

Jacob Pressures

SSCertifiable

Points: 5804
More actions
April 26, 2007 at 8:49 am

#69004

How do i import these files and their data from one SQL Server to another?
Will doing so recreate the previous databases?
Should i just copy and paste over the existing files in the New Server?
What if the newer DB is an improvement but same structure? Can i just load teh old one and import the data into the new one somehow?
Thanks! Here is the code i used to create the original Database that i want to move. The new database (on different computer) has similar files--same names, but empty.
Use master
GO

CREATE DATABASE FAE
ON PRIMARY
(
NAME       = FAE_Data1,
FILENAME   = 'c:\program files\microsoft sql server\mssql\data\FAE_Data1.mdf',
MAXSIZE    = 50GB,
SIZE       = 20MB,
FILEGROWTH = 10%
),
(
NAME       = FAE_Data2,
FILENAME   = 'c:\program files\microsoft sql server\mssql\data\FAE_Data2.ndf',
MAXSIZE    = 50GB,
SIZE       = 20MB,
FILEGROWTH = 10%
)

LOG ON
(
NAME       = FAE_log1,
FILENAME   = 'c:\program files\microsoft sql server\mssql\data\FAE_log1.ldf',
MAXSIZE    = 1GB,
SIZE       = 10MB,
FILEGROWTH = 10%
),
(
NAME       = FAE_log2,
FILENAME   = 'c:\program files\microsoft sql server\mssql\data\FAE_log2.ldf',
MAXSIZE    = 1GB,
SIZE       = 10MB,
FILEGROWTH = 10%
)
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

I'm not sure I'm following all your questions. Are you saying all you have are the data files? If that is the case you might look up the "Attach database" command. In the event that the old server and new server are both availalbe to you and the old server has a functioning database I would do the following:

--Run backup command on the database you want to move

BACKUP DATABASE [FAE] to DISK='C:\temp\FAE.bak' with format

go

--Copy the backup file from the old server to the new server (I'll just assume you can get it from the C:\temp on one server to the C:\Temp on the other)

--on new server run the restore command

RESTORE DATABASE [FAE]

FROM DISK='C:\temp\FAE.bak'

WITH

REPLACE,

MOVE 'FAE_Data1' TO 'C:\SQLData\FAE_Data1.mdf',

MOVE 'FAE_Data2' TO 'C:\SQLData\FAE_Data2.ndf',

MOVE 'FAE_Log1' TO 'C:\SQLData\FAE_Log1.ldf',

MOVE 'FAE_Log2' TO 'C:\SQLData\FAE_Log2.ldf'

go

--NOTE: You really only need the "Move" command if you are changing the location or name of the physical files of the

-- database on the new machine. Otherwise a straight restore command should work fine.

--If you have created user logins in the database those will need to be recreated on the new server.

Hope that helps.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 2

I found this information on the subject but I trying to understand it.

http://support.microsoft.com/kb/314546/

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 3

Thanks JLK, that was helpful!

This article was the most helpful.

http://support.microsoft.com/kb/224071/

Thanks!