February 16, 2009 at 4:17 pm
Hello
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\DATAewDatabase.mdf',
MOVE 'oldDataBase_Log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\DATAewDatabase_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.
Regards
James
February 16, 2009 at 10:04 pm
You don't need to create the blank database, just do the restore.
The command looks OK, what I might suggest is that you run the SQL you need, get that working, and then go through building the string.
February 16, 2009 at 10:34 pm
I have tried without the database being created beforehand but the error is the same.
February 16, 2009 at 10:58 pm
Solved it - my bad (lack of understanding). In the MOVE I was using the physical 'filename' not the logical 'name'.
February 17, 2009 at 6:58 am
Doh! Good catch and glad it's working.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply