I have a requirement to perform a large number of updates within a SQL database from C#. Essentially I am merging changes made to one copy of a database into a master database.
I originally was thinking of using a transaction but this would not work in my case as the merge calls loads of stored procedures and sql queries which create their own connection.
I then decided to create a backup copy of the database being updated, update the copy, delete the original database and rename the copy to ensure that if anything went wrong during the update that my master database was left intact.
I am using the SMO classes to create the copy of the main database and have hit a problem - even though I have changed the name of the new database the SMO classes seem to want to create the same file names for the .MDF and .LDF files which then give an error from the call to
Are there any options which I can specify on the transfer which will change the names of the physical files created? I have specified that the new database should be created if it does not exist.