Need to make a copy of database from a previous backup

Question

Need to make a copy of database from a previous backup

dndaughtery

SSChampion

Points: 11286
More actions
April 8, 2010 at 11:14 am

#217545

I made a mistake in my database I'll call StateCaseLevelData. I have a back up of it. I want to create another db called StateBU with the backup for StateCaseLevelData. This is what I've done so far:
created a new db named StateBU then tried right clicking going to tasks/restore database and select the "From Device" and selected the old backup of StateCaseLevelData as the backup file and clicked the restore checkbox. When I try to run it says
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'TEST-SQL-1'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The file 'D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\StateCaseLevelData.mdf' cannot be overwritten. It is being used by database 'StateCaseLevelData'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
How can I accomplish this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

you will need to select the overwrite option. Also, be certain that the database files are not in the same location as the previous database, or at least rename the files.

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

dndaughtery SSChampion Points: 11286 More actions · Answer 2

dndaughtery

SSChampion

Points: 11286

April 8, 2010 at 11:24 am

#1148500

Many Thanks

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

dndaughtery (4/8/2010)
I made a mistake in my database I'll call StateCaseLevelData. I have a back up of it. I want to create another db called StateBU with the backup for StateCaseLevelData. This is what I've done so far:
created a new db named StateBU then tried right clicking going to tasks/restore database and select the "From Device" and selected the old backup of StateCaseLevelData as the backup file and clicked the restore checkbox. When I try to run it says
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'TEST-SQL-1'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The file 'D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\StateCaseLevelData.mdf' cannot be overwritten. It is being used by database 'StateCaseLevelData'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
How can I accomplish this?

restore database StateBU

from disk = N'StateCaseLevelDat.bak'

with file = 1,

move N'StateCaseLevelData' to N'StateBU.mdf',

move N'StateCaseLevelLog' to N'StateBU.ldf',

replace,

recovery,

stats = 10;

Something like that, but you need to make the appropriate changes so that this actually works. Please read about the RESTORE BACKUP command in Books Online, it will give you the necessary info you need to make the needed changes.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

You're welcome.

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