June 28, 2006 at 1:08 pm
I am running SQL Server 2000 on Windows 2000 ASE. I have a development and production cluster. The DBA before me appears to have created 2 seperate datafiles (38 gb on G: and 2 gb P: drive... think there was space issue at the time) on a primary filegroup on the production environment. The development environment simply has 38 gb on a single drive. When trying to restore the production environment to the development its gives me 3 files to restore when only 2 are currently in place on development. Do I need to drop the development database in place and create a new db with 2 data files in order to get this to restore properly? I have tried to restore to no avail over the last 2 days. Any suggestions are greatly appreciated.
June 28, 2006 at 2:30 pm
Your problem is that when you do restore SQL server will try and restore the files to their original locations and if you go to different server with different logical drives and/or less drives then it ain't gonna work.
What you need to do is do a RESTORE FILELISTONLY e.g.
RESTORE
FILELISTONLY FROM DISK = N'E:\TestDB\TestDB_backup_200606051749.bak'
This will give you the logical files names of the files contained in the backup then you need to do a RESTORE WITH MOVE e.g.
RESTORE
DATABASE [TestDB]
FROM
DISK = N'E:\TestDB\TestDB_backup_200606051749.bak' WITH FILE = 1,
MOVE
N'TestDB_System_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_System_Data.mdf',
MOVE
N'TestDB_System_Data2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_System_Data2.ndf',
MOVE
N'TestDB_Flights_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_Flights_Data.ndf',
MOVE
N'TestDB_Flights_Data2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_Flights_Data2.ndf',
MOVE
N'TestDB_Lookups_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_Lookups_Data.ndf',
MOVE
N'TestDB_Lookups_Data2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_Lookups_Data2.ndf',
MOVE
N'TestDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_log.ldf',
MOVE
N'TestDB_Log2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_Log2.ldf',
MOVE
N'TestDB_FullText_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_FullTextData_Data.mdf',
MOVE
N'TestDB_FullText_Data2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TestDB_FullTextData_Log2.ldf',
REPLACE
;
GO
This example has quite a few logical files but should give you the gist of what you need to do. BOL can provide further clarification on this
hth
David
June 29, 2006 at 6:00 am
Thanks David! I will give this the old college try.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply