How to copy an SQL database from one PC to another programatically? RRS feed

  • Question

  • My App uses a database (SQL Server 2008) and i would like the user to have easy to use buttons like "Export" and "Import" so that he can save the data to a pen drive or so and then import it in another PC and achieve the same configuration.

    How can I do this?

    Wednesday, April 4, 2012 10:35 AM


All replies

  • Hi

    I would like to have Export and Import methods both accepting a path to a file. The Export method would save my database to a file, then i would copy this and paste it in my other PC. Then i would call the Import method and hopefully see reflected all the changes i made on the other instance.

    I'm using SQL Server 2008 and Entity Framework 4.0

    Thanks in advance

    • Moved by Bob Beauchemin Thursday, April 5, 2012 6:09 AM Moving to an appropriate forum. Also, try posting to "ADO.NET" managed provider forum. (From:.NET Framework inside SQL Server)
    • Merged by Allen_MSDNModerator Friday, April 6, 2012 2:03 AM
    Wednesday, April 4, 2012 10:38 PM
  • Hello,

    To archive this you have to backup the database, copying the backup file to the other server and restore the database there.

    BTW, this forum is for .NET inside SQL Server = CLR.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Thursday, April 5, 2012 4:30 AM
  • Using Windows Forum App to transfer data from one PC to another?

    Fighting like Allen Iverson. Neve give up!

    Thursday, April 5, 2012 5:55 AM
  • Hi again, thanks for the tips.

    This is what i have so far:

    By default the SQL service has writing permissions in a special folder inside Program Files (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\). I tried to create the backups in another place (my desktop) and got an "Access Denied" message. That's why I first create the backup and then copy that file to the location I want.

    The Export(string) method works well. But in the Import method I get the following message when calling to ExecuteNonQuery:"Exclusive access could not be obtained because the database is in use." I'm trying to close the current connection before but it's not working.

    void Export(string destination_file) {

    //context is the ObjextContext you use when working with EntityFramework context.ExecuteStoreCommand("BACKUP DATABASE @database TO DISK = @file", new SqlParameter { ParameterName = "database", Value = target_database }, new SqlParameter { ParameterName = "file", Value = temp_backup }); File.Copy(backup_folder + temp_backup, destination_file); } void Import(string source_file) { string destination_file = backup_folder + temp_backup; File.Copy(source_file, destination_file); //i try to close the connection but still doesn't work context.Connection.Close(); SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(); sqlBuilder.DataSource = "(local)"; sqlBuilder.InitialCatalog = "master"; sqlBuilder.IntegratedSecurity = true; string conString = sqlBuilder.ToString(); using (SqlConnection connection = new SqlConnection(conString)) { connection.Open(); SqlCommand command = new SqlCommand("RESTORE DATABASE @database FROM DISK = @file", connection); SqlParameter dbParam = new SqlParameter("database", target_database); SqlParameter fileParam = new SqlParameter("file", temp_backup); command.Parameters.Add(dbParam); command.Parameters.Add(fileParam); command.ExecuteNonQuery(); } }

    • Edited by agugglez Friday, April 6, 2012 7:42 PM
    Friday, April 6, 2012 7:32 PM
  • This link will help.;en-us;Q314546

    Fighting like Allen Iverson. Neve give up!

    Monday, April 9, 2012 2:29 AM
  • Is there anyone except you using the database?

    Fighting like Allen Iverson. Neve give up!

    Wednesday, April 11, 2012 4:09 AM