2

As per the below reference document, I am looking to move a SQL Server Express database (localdb created within vs2013).

How to move a localdb from one machine to another manually

However the operation needs to be entirely automated from within my wpf c# application.

What I want to achieve is (updated):

  1. Initial Application implementation (Currently most important) ->

    The database needs to be installed / copied to a users machine when the application is installed, ideally the database needs to be packaged in a zip or installer file, so it can be easily transferred to the users machine when installing the application, or I'm open to any better ideas?

  2. Keeping it upto date -> The application needs to be able to export and import data from the localdb to a sql database file on a local network share. This may require merging of data.

Whether it be done via c# code or sql script (if script please advise how to run a script from c#) How could one best achieve this?

Thanks for your help in advance!

Improve this question
2
  • I'm not a C# guy so I can't fully answer your question. However can you if you're just using a back-up why not just use a SQL command to make the back-up and then move it with your C# script and use a SQL command to restore it. Commented Dec 18, 2013 at 21:14
  • If you're using entity framework, you could just have entity framework install the database plus all the initial data when the application is first run. After EF installs the database, you would then use Sync framework to update between the two instances of database files. Commented Dec 20, 2013 at 16:27

1 Answer 1

Reset to default
1

based on your question you're trying to simply transfer a database from one machine to another correct? You'll need to consider some variables, for instance do you need to create the database? How will the application speak to each machine? Several other variables will need to be considered; however the route I would more than likely take is create a desired web-service.

The web-service would allow you to decide if your pulling a local database to the server, or the server to your local machine, or any requirements that may be required.

Without those requirements the task becomes difficult. However, if you think of your issue on a smaller scale, it becomes quite clear.

  1. Verify the primary database exist
  2. Issue a command to take a backup
  3. Create a new database
  4. Verify your newly created database exist
  5. Run a command to restore a backup

That is essentially all you need to accomplish, a single class could do that.

You would need to create the logic for the methods this calls; but is an idea to do your request on the same machine; which isn't that different from your request.

if(IsDatabaseInExistence(server.TemplateName) == true)
     CreateSQLDatabase(customer.WebAddress);

if(IsDatabaseInExistence(customer.WebAddress) == true)
     RestoreSQLDatabase(server.TemplateName, customer.WebAddress);

A method that takes parameters, then call a couple private methods and you've accomplished your task. On your quest though to accomplish, try to use parameters, if you issue raw SqlCommand you can make it susceptible to SQL Injection.

Bad Practice:

using(SqlConnection connectionForSQL = new SqlConnection(@"Server=localhost; Integrated Security=SSPI; Database=master"))
{
     string restoreSQLDb =
           "RESTORE FILELISTONLY FROM DISK='C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup\\" + templateName + ".bak'"
           + "RESTORE DATABASE [" + webAddress + "] FROM DISK='C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup\\" + templateName + ".bak'"
           + "WITH " 
           + "MOVE 'Parent' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\" + webAddress + ".mdf',"
           + "MOVE 'Parent_log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\" + webAddress + "_log.ldf',"
          + "REPLACE";

          using(SqlCommand restoreDbCommand = new SqlCommand(restoreSQLDb, connectionForSQL))
          {
               connectionForSQL.Open();
               restoreDbCommand.ExecuteNonQuery();
          }
}

Also, the example I forgot to issue a backup method but you get the idea.

However I think your issue is getting it from one machine to another. I would look into Windows Communication Foundation Services (WCF). This should provide the flexibility to communicate with another machine and accomplish an assortment of task.

Unfortunately, I don't have exact requirements so it makes it truly difficult to help you.

Hopefully this points you in the right direction though.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Step 3&4 are not necessary. SQL Server will automatically create the DB when you specify the DB name as a part of the RESTORE command. Indeed, you actually have to specify WITH REPLACE to overwrite an existing DB with the command. You must DROP the DB first otherwise. I'd say it'd be better practice to warn the user when the restore target exists.
After giving it more thought, I've updated what I'm trying to achieve above. Keeping in mind there are no web servers available, just a network file share and the users machine.
@MikeDub You can still use WCF to transmit locally, with various protocols such as TCP.
Thanks, I've accepted it as the answer, even though it is not a complete solution it at least points me in the right direction. I believe.
@MikeDub I have web-service I wrote to automate Content Management System deployments; with a custom database we wrote. Except it all existed on the same server; however similar should apply. If you need anything else feel free to ask.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.