July 22, 2015 at 2:08 pm
Hi
i am trying to restore bak file using the following query
<
RESTORE DATABASE WTS
FROM DISK = 'C:\Gautam\WTS_BLR_PRE_LOADTEST.BAK'
WITH MOVE 'WTS_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.mdf',
MOVE 'WTS_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.ldf'
GO
>
But the above query is throwing the following error
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_1.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Master_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_1.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_2.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Work_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_2.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_3.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Indx_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_3.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_4.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Image_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_4.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_6.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Log3' cannot be restored to 'D:\SQLDATA\WTS_BLR_6.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Could any one help me?
regards
Gautam
July 22, 2015 at 4:15 pm
sendtog.roy (7/22/2015)
Hii am trying to restore bak file using the following query
<
RESTORE DATABASE WTS
FROM DISK = 'C:\Gautam\WTS_BLR_PRE_LOADTEST.BAK'
WITH MOVE 'WTS_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.mdf',
MOVE 'WTS_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.ldf'
GO
>
But the above query is throwing the following error
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_1.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Master_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_1.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_2.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Work_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_2.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_3.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Indx_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_3.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_4.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Image_Data' cannot be restored to 'D:\SQLDATA\WTS_BLR_4.ndf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQLDATA\WTS_BLR_6.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'WTS_Log3' cannot be restored to 'D:\SQLDATA\WTS_BLR_6.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Could any one help me?
regards
Gautam
Each of the additional files specified also requires the Move command like you did for the first data file in your restore command
Just specify the logical names of these other files and the path you wish to move it to like in this snippet:
,
MOVE 'additional logical file name' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\additional physical name.ndf'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 23, 2015 at 3:21 am
you have more than one data file .you should add "move to " for them.
July 23, 2015 at 12:10 pm
To get the list of database files in the backup (you will need to provide a new physical location for each file using the MOVE TO clause):
RESTORE FILELISTONLY FROM DISK = 'C:\Gautam\WTS_BLR_PRE_LOADTEST.BAK'
Note the first two columns in the output: LogicalName and PhysicalName. Using MOVE TO, you will specify a new location for each LogicalName in the output.
RESTORE DATABASE WTS
FROM DISK = 'C:\Gautam\WTS_BLR_PRE_LOADTEST.BAK'
WITH MOVE 'WTS_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.mdf',
MOVE 'WTS_Master_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQLDATA\WTS_BLR_1.ndf',
MOVE 'WTS_Work_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS_BLR_2.ndf',
-- and more of these to get every file
MOVE 'LogicalName from FILELISTONLY output' TO 'new location',
MOVE 'WTS_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.ldf'
GO
-Eddie
Eddie Wuerch
MCM: SQL
July 23, 2015 at 12:21 pm
Eddie Wuerch (7/23/2015)
To get the list of database files in the backup (you will need to provide a new physical location for each file using the MOVE TO clause):
RESTORE FILELISTONLY FROM DISK = 'C:\Gautam\WTS_BLR_PRE_LOADTEST.BAK'Note the first two columns in the output: LogicalName and PhysicalName. Using MOVE TO, you will specify a new location for each LogicalName in the output.
RESTORE DATABASE WTSFROM DISK = 'C:\Gautam\WTS_BLR_PRE_LOADTEST.BAK'
WITH MOVE 'WTS_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.mdf',
MOVE 'WTS_Master_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQLDATA\WTS_BLR_1.ndf',
MOVE 'WTS_Work_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS_BLR_2.ndf',
-- and more of these to get every file
MOVE 'LogicalName from FILELISTONLY output' TO 'new location',
MOVE 'WTS_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WTS.ldf'
GO
-Eddie
Making it soooo easy there...
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply