May 16, 2012 at 8:04 am
Hi Guys,
I have recently backed up my data warehouse database, now I’m trying to restore the database in my VM Virtual Box but its giving me error.
Please Note that i previously had the same database on my VM and detached it and i deleted the .MDF and LDF files, later i copied over the new .bak database and i can’t restore it.
ERROR
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'BGCPC01'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BGPhoenix.mdf' is claimed by 'msphxx_index001'(3) and 'msphxx_data001'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
May 16, 2012 at 8:18 am
There must be another database that's using the same physical file names. Use the WITH MOVE clause as the error message suggests, and it should work.
John
May 16, 2012 at 8:28 am
John Mitchell-245523 (5/16/2012)
There must be another database that's using the same physical file names. Use the WITH MOVE clause as the error message suggests, and it should work.John
John the error message is not clear and does not direct to the right issue.. remember i cleared the DATA files were all .mdf, .ldf and .ndf files are stored.
What was happening is, the restore is trying to restore .mdf, .mdf, .ldf --> this leads to a conflict in the restore of restoring the same file to the same file.
What i did is renamed one msphxx_index001 to .ndf for the indexing, and made sure that the file names are actual the same.
Thanks
May 16, 2012 at 8:39 am
So are you saying that if you look on the disk, the file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BGPhoenix.mdf doesn't exist? If that's the case, then I imagine you're trying to use the same file twice in your restore.
I strongly recommend that you use the RESTORE DATABASE command instead of the GUI. You then have a statement that you can use again and again. This is especially helpful when trying to troubleshoot a problem like this - you can just tweak the SQL and run it again instead of doing all those tedious clicks in the GUI every time.
In order to help us to help you out of this particular corner, please will you press the Script button at the top of the Restore Database window and post the ensuing SQL?
John
May 16, 2012 at 9:01 am
John this is the first error
CREATE DATABASE [BGPhoenix] ON PRIMARY
( NAME = N'msphxx_data001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf' , SIZE = 25853440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [SEG_INDEX]
( NAME = N'msphxx_index001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf' , SIZE = 3749440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'msphxx_log001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_log.ldf' , SIZE = 39424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
GO
Than i changed it to
CREATE DATABASE [BGPhoenix] ON PRIMARY
( NAME = N'msphxx_data001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf' , SIZE = 25853440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [SEG_INDEX]
( NAME = N'msphxx_index001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_index.ndf' , SIZE = 3749440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'msphxx_log001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_log.ldf' , SIZE = 39424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
GO
May 16, 2012 at 9:03 am
shani19831 (5/16/2012)
John this is the first errorCREATE DATABASE [BGPhoenix] ON PRIMARY
( NAME = N'msphxx_data001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf' , SIZE = 25853440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [SEG_INDEX]
( NAME = N'msphxx_index001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf' , SIZE = 3749440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'msphxx_log001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_log.ldf' , SIZE = 39424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
GO
Than i changed it to
CREATE DATABASE [BGPhoenix] ON PRIMARY
( NAME = N'msphxx_data001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf' , SIZE = 25853440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [SEG_INDEX]
( NAME = N'msphxx_index001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_index.ndf' , SIZE = 3749440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'msphxx_log001', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_log.ldf' , SIZE = 39424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
GO
Where is the RESTORE DATABASE script you were asked to provide? The code above is for creating a database, not restoring one from a backup.
May 16, 2012 at 9:09 am
That isn't an error, it's a CREATE DATABASE statement. Is that what you get when you press the Script button in the Restore Database window? No RESTORE statement?
Now, the first statetment will clearly fail, since you are using the same file name twice. The second one will fail if one of the file names already exists on disk. Are you getting exactly the same error message each time? If not, please post both.
John
May 16, 2012 at 9:21 am
John, since i got it working.... i will try to reverse the process and regenerate the same error and post it here lets say tomorrow....
thanks anyways
May 18, 2012 at 4:11 am
shani19831 (5/16/2012)
John, since i got it working.... i will try to reverse the process and regenerate the same error and post it here lets say tomorrow....thanks anyways
Ok heres the initial restore
RESTORE DATABASE Phoenix
FROM DISK = 'C:\temp\Phoenix.bak'
WITH
MOVE 'Phoenix' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf',
MOVE 'Phoenix_index' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf',
MOVE 'Phoenix_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_log.ldf'
The fix that i applied
RESTORE DATABASE Phoenix
FROM DISK = 'C:\temp\Phoenix.bak'
WITH
MOVE PhoenixTO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.mdf',
MOVE Phoenix_index' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix.ndf',
MOVE Phoenix_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Phoenix_log.ldf'
May 18, 2012 at 4:22 am
OK, thanks for posting back. Turns out that you were specifying the same physical file twice in the same RESTORE statement.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply