September 12, 2011 at 3:40 pm
Hello all,
I have been tasked with automating a database restore to a test database to run daily. I wrote the following script:
ALTER DATABASE "Ken-Test" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
RESTORE DATABASE "Ken-Test"
FROM DISK = 'f:\mssql\backup\Mun-EaseMainWebDb\muneas_backup.bak'
WITH
MOVE 'Mun-EaseEmptyDb_Data' TO 'f:\mssq\data\Test\Ken-Test_Data.mdf',
MOVE 'Mun-EaseEmptyDb_Log' TO 'f:\mssq\data\test\Ken-Test_Log.ldf',
REPLACE
go
ALTER DATABASE "Ken-Test" SET MULTI_USER
When I run the code I get the following error stack:
Server: Msg 5105, Level 16, State 2, Line 2
Device activation error. The physical file name 'f:\mssq\data\Test\Ken-Test_Data.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 2
File 'Mun-EaseEmptyDb_Data' cannot be restored to 'f:\mssq\data\Test\Ken-Test_Data.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 2
Device activation error. The physical file name 'f:\mssq\data\test\Ken-Test_Log.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 2
File 'Mun-EaseEmptyDb_Log' cannot be restored to 'f:\mssq\data\test\Ken-Test_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
To date I have done the following:
1. Verifyed the name of the logical files - they are correct
2. Verifyed the path of the physical files - they are correct
3. It does not matter if I have the Ken-test database prcreated or not I get the same error
4. I can restore the Ken-Test database using restore in Enterprise Manager
Not sure what I am doing wrong, any help is greatly appreciated!
Ken
September 12, 2011 at 4:44 pm
RESTORE DATABASE "Ken-Test"
FROM DISK = 'f:\mssql\backup\Mun-EaseMainWebDb\muneas_backup.bak'
WITH
MOVE 'Mun-EaseEmptyDb_Data' TO 'f:\mssq\data\Test\Ken-Test_Data.mdf',
MOVE 'Mun-EaseEmptyDb_Log' TO 'f:\mssq\data\test\Ken-Test_Log.ldf',
You're missing the l in sql. You've got mssq not mssql.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 9:43 am
Gail,
That was it!! Guess there really is something to 'another set of eyes'! Thanks so much!
Ken 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply