Using SMO to copy an SQL database

Posted on 2011-04-27
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


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.
Question by:ChrisMDrew
    LVL 10

    Accepted Solution

    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!


    Author Closing Comment

    Thanks - just what I needed

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    In my previous article ( we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now