I have a VB.NET application that needs to backup a database then restore to a new database in the same Server as I am upgrading an application and I want to retain the existing database.
I create a blank database using the following:
cmdSA.CommandText = "CREATE DATABASE [newDatabase]" cmdSA.ExecuteNonQuery()
I then backup the oldDataBase and attempt to restore the backup to the newDatabase with the following code:
cmd.CommandText = "BACKUP DATABASE [" & txtOldCatalog.Text & "] TO DISK='" & buName & "'" x = cmd.ExecuteNonQuery() With cmdSA .CommandText = "USE [" & txtNewCatalog.Text & "]" .ExecuteNonQuery() .CommandText = "RESTORE FILELISTONLY FROM DISK='" & buName & "'" .ExecuteNonQuery() .CommandText = "USE master" .ExecuteNonQuery() .CommandText = "RESTORE DATABASE [" & txtNewCatalog.Text & "] FROM DISK='" & buName & "' WITH " _ & "MOVE '" & mdfName & "' TO '" & newMDFName & "', " _ & "MOVE '" & logName & "' TO '" & newLogName & "', REPLACE" .ExecuteNonQuery() End With
The CommandText for the RESTORE DATABASE resolves to:
RESTORE DATABASE [newDatabase] FROM DISK='C:\Development\Upgrade_to_V6\bin\Debug\oldDataBase_8_54_49.bak' WITH MOVE 'oldDataBase_Data' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\newDatabase.mdf', MOVE 'oldDataBase_Log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\newDatabase_Log', REPLACE
When it executes I get the following error message:
Logical file 'oldDataBase_Data' is not part of database 'newDatabase'. Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally.
Can anyone tell me how to achieve what I need.