Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using SMO to copy an SQL database

Posted on 2011-04-27
2
Medium Priority
?
268 Views
Last Modified: 2013-12-16
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

transfer.TransferData()

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.
0
Comment
Question by:ChrisMDrew
2 Comments
 
LVL 10

Accepted Solution

by:
oxyoo earned 2000 total points
ID: 35480705
The RelocateFile class has a PhysicalFileName [1] property that you should be able to set with your path and new name. You might also be interested in the answer at here [2], where there is an example on how to use this property.


Good Luck!


 [1]: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.relocatefile.physicalfilename.aspx
 [2]: http://www.eggheadcafe.com/software/aspnet/32188436/smorestore-database-name-change.aspx
0
 

Author Closing Comment

by:ChrisMDrew
ID: 35511382
Thanks - just what I needed
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question