Solved

Database Recovery from Script or Task fails

Posted on 2014-11-30
3
172 Views
Last Modified: 2014-12-01
I made a job to backup my database, MUM2, and named it MYMHalfHour.bak

I'm trying to create a script that will recover the database from this backup, to a development copy, called MYM_Dev1.

I use this script:

RESTORE DATABASE [MYM_Dev1] FROM  
DISK = N'C:\Backups\MYMBups\MYMHalfHour.bak' 
WITH  FILE = 1,  MOVE N'MUM2' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MYM_Dev1.mdf', 
 MOVE N' MUM2_log' 
 TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MYM_Dev1_1.ldf', 
  NOUNLOAD,  STATS = 10

Open in new window


I get this error:
Logical file 'MUM2' is not part of database 'MYM_Dev1'. Use RESTORE FILELISTONLY to list the logical file names.

If I use the IDE to try to do a restore: Tasks | Restore | Database:
Select Device: C:\backups\MYMHalfHour.bak
Database: Mum2
Destination:
Database: MYM_Dev1

I get an error:
System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Mum2.mdf' cannot be overwritten.  It is being used by database 'Mum2'. (Microsoft.SqlServer.SmoExtended)

Which I don't understand, because I'm not restoring to MUM2, I'm restoring to MYM_Dev1. I even took MUM2 offline and tried it and get the same error.

But when I go to the Files tab, the Logical File name for the files is not MUM2, but is:
SSSBase2012
SSSBase2012_log

which is the name of another database on this server.

So I've tried two different methods and both have different errors. Please help, what am I doing wrong?

Also, if I run the script: RESTORE FILELISTONLY I get this:
SSSBase2012       C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Mum2.mdf      D      SSSBase2012_log      C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Mum2_1.ldf      L

So I tried this script:
RESTORE DATABASE [MYM_Dev1] FROM  
DISK = N'C:\Backups\MYMBups\MYMHalfHour.bak' 
WITH  FILE = 1,  MOVE N'SSSBase2012' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MYM_Dev1.mdf', 
 MOVE N' SSSBase2012_log' 
 TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MYM_Dev1_1.ldf', 
  NOUNLOAD,  STATS = 10

Open in new window


But it errors with:
Logical file ' SSSBase2012_log' is not part of database 'MYM_Dev1'.

thanks.
0
Comment
Question by:Starr Duskk
3 Comments
 
LVL 5

Expert Comment

by:Trent Smith
ID: 40472730
Not sure if this will work or not but try it and let me know.

Alter Database MYM_Dev1

Set Single_User with Rollback Immediate

Restore Database MYM_Dev1
From Disk = 'C:\Backups\MYMBups\MYMHalfHour.bak'
With Replace, Stats = 5
0
 
LVL 7

Accepted Solution

by:
Dung Dinh earned 500 total points
ID: 40473145
Hi,

I'm not sure but logical name of file log makes me confuse here. Why does it contain white space?

 MOVE N' SSSBase2012_log'
 TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MYM_Dev1_1.ldf',
  NOUNLOAD,  STATS = 10
0
 
LVL 2

Author Closing Comment

by:Starr Duskk
ID: 40473192
Dung,

That was it! The space within the single quote broke it!

thanks for the sharp eyes!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question