ggssu


Hi,

I was wondering how I could restore a bkp file into a database with a different name. For example, I made a backup of T1.mdf and i want to restore t1.bkp to T2.mdf.

I am using VB.Net to do the backup and restore.

Is there any way of doing it

Thanks!




Re: How to restore backup database to a different database? Please its urgent

Jens K. Suessmeyer


Are you using SMO or just plain SQL Command

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---







Re: How to restore backup database to a different database? Please its urgent

ggssu

I am using SQLDMO

Sand






Re: How to restore backup database to a different database? Please its urgent

Jens K. Suessmeyer

If you can use VB.NET (FW. 2.0) you should use SMO instead, DMO is the COM-based library to administer SQL Server. SMO is the new .NET library for adminstration.

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---






Re: How to restore backup database to a different database? Please its urgent

ggssu

how do i go about doing a backup and restore using SMO

Sand





Re: How to restore backup database to a different database? Please its urgent

Jens K. Suessmeyer

Have a look here:

http://www.codeproject.com/useritems/BackupRestoreWithSmo.asp

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---






Re: How to restore backup database to a different database? Please its urgent

ggssu

Hi,

I tried the restore and backup method using SMO, and i get the following error

"The backup set holds a backup of a database other than the existing database". What I am trying to achieve is restore my data into another database that resides in the same server as my source database

Sand





Re: How to restore backup database to a different database? Please its urgent

Jens K. Suessmeyer

Do you want to overwrite the existing database Or do you want to create a new one YOu cannot "merge" the databases (the old one an the one from the backup set, only the mentioned options are possible)

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---






Re: How to restore backup database to a different database? Please its urgent

ggssu

I don't want to merge the database. I want to create a new database with a different name. Which means i want to restore the bkp file to a new database on the same server.

Sand





Re: How to restore backup database to a different database? Please its urgent

Jens K. Suessmeyer

Sepcify the parameters like in the sample from the mentioned link:

myRestore.Database = destinationDatabaseName;

And (if it the files were from the same server) relocate the files if the files within the backup file are already existing on the server.

Jens K. Suessmeyer


---
http://www.sqlserver2005.de
---






Re: How to restore backup database to a different database? Please its urgent

ggssu

I don't quite understand what you were trying to say in your previous post. Is it possible if you could explain it further. Below is my code snippet for your reference. The database from which the .bak file was back up to is known as "EMS". Using that back up file i am trying to restore it to EMSArchieve. Both EMS and EMSArchieve reside in the same server.

Code Snippet

Dim destinationDatabaseName As String = "EMSArchieve"

Dim serverPath As String = My.Computer.Name

Dim svr As Server = New Server(serverPath & "\SQLEXPRESS")

Dim restore As Microsoft.SqlServer.Management.Smo.Restore = New Microsoft.SqlServer.Management.Smo.Restore()

restore.Database = destinationDatabaseName

Dim currentDb As Database = svr.Databases(destinationDatabaseName)

If (Not currentDb.ToString = "") Then

svr.KillAllProcesses(destinationDatabaseName)

End If

restore.Devices.AddDevice(backupFile, DeviceType.File)

Dim DataFileLocation As String = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMSArchieve.mdf"

Dim LogFileLocation As String = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMSArchieve_log.ldf"

restore.RelocateFiles.Add(New RelocateFile("EMSArchieve", DataFileLocation))

restore.RelocateFiles.Add(New RelocateFile("EMSArchieve", LogFileLocation))

restore.ReplaceDatabase = True

Console.WriteLine("Restoring:{0}", destinationDatabaseName)

restore.SqlRestore(svr)

currentDb = svr.Databases(destinationDatabaseName)

currentDb.SetOnline()

MsgBox("Successfully Restored database")





Re: How to restore backup database to a different database? Please its urgent

ggssu

I get a new error as follows,

"Restore fail for server 'servername'

Any idea why the above error occurs





Re: How to restore backup database to a different database? Please its urgent

Jens K. Suessmeyer

There should be more information in the InnerException, try first something simple like connecting to the server and querying the server information to make sure the connection can be established successfully.

Console.WriteLine(new Server().Information.VersionString);

Btw, are Remote connections enabled for the Server



Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---






Re: How to restore backup database to a different database? Please its urgent

ggssu

The following InnerException is produced

Restoring:EMSArchieve

Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'SANDHYA\SQLEXPRESS'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMS.mdf' cannot be overwritten. It is being used by database 'EMS'.

File 'EMS' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMS.mdf'. Use WITH MOVE to identify a valid location for the file.

The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMS_log.ldf' cannot be overwritten. It is being used by database 'EMS'.

File 'EMS_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMS_log.ldf'. Use WITH MOVE to identify a valid location for the file.

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

RESTORE DATABASE is terminating abnormally.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

--- End of inner exception stack trace ---

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)

at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)

at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)





Re: How to restore backup database to a different database? Please its urgent

ggssu

I can establish a connection to the server, as i can retrieve the version info

Sand