June 12, 2008 at 8:37 am
I am doing migration from SQL Server 2000 to 2005.
I am trying BACKUP/RESTORE approach. One of the production databases
"Commission_T59_DSL" has two data files located on different drives:
File name Location
---------------------------------------------------------------------
"Commission_DPH_Data" F:\sqldata\MSSQL\data\Commission_T59_DPH_Data.MDF
"commission_t59_DSL_1_Data" D:\sqldata\Commission_t59_DSL2
I didn't realize it at the time I was creating a backup file.
Now when I run
USE master--
GO
RESTORE DATABASE Commission_T59_DSL
FROM DISK = 'D:\MSSQL\Backup\Commission_T59_DSL.bak'
WITH RECOVERY,
STATS = 1, --% completed
MOVE 'Commission_T59_DPH_Data' TO 'D:\MSSQL\Data\Commission_T59_DSL_data.mdf',
MOVE 'Commission_T59_DPH_Log' TO 'D:\MSSQL\Data\Commission_T59_DSL_log.mdf'
GO
It gives me an error:
--error: Directory lookup for the file "D:\sqldata\commission_t59_DSL2" failed with the operating system error 2(The system cannot find the file specified.).
Was anything wrong with the way I created a backup file?
How does it affect BACKUP/RESTORE process the fact that there are two data files?
June 12, 2008 at 9:01 am
My mistake.
I had to move each file.
This works....
RESTORE DATABASE Commission_T59_DSL
FROM DISK = 'D:\MSSQL\Backup\Commission_T59_DSL.bak'
WITH RECOVERY,
STATS = 1, --% completed
MOVE 'Commission_T59_DPH_Data' TO 'D:\MSSQL\Data\Commission_T59_DSL_data.mdf',
MOVE 'commission_t59_DSL_1_Data' TO 'D:\MSSQL\Data\Commission_T59_DSL_data2.mdf',
MOVE 'Commission_T59_DPH_Log' TO 'D:\MSSQL\Data\Commission_T59_DSL_log.ldf',
MOVE 'commission_t59_DSL_1_Log' TO 'D:\MSSQL\Data\Commission_T59_DSL_log2.ldf'
I don't see any reason why the data file has to be split in two.
Can I merge them?
June 12, 2008 at 12:32 pm
It can be different reason to use two data files...
1. performance
2. disk space
etc...
Yes, you can merge then...
If the files are on two different file groups then move the data and objects to one file group and empty the file and delete...
If both files are under on filegroup then you can try to empty the file before deleting it...
Check DBCC SHRINKFILE command for more details to empty file...If the file is big it may take longer...
MohammedU
Microsoft SQL Server MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply