Migrate/Import entire database from one SQL instance to another

I’ve been trying to figure out the best way to import/export databases between my development SQL instance and my production SQL instance for a few days and I think I found my preferred method.

Here’s my step by step for transferring my entire database from one machine to another:

  1. Run SQL Server Configuration Manager and stop the service named “SQL Server (instance name)” for the database you wish to overwrite. You won’t have permission to overwrite the db otherwise.
  2. Copy 2 files named “[database name].mdf” and “[database name]_log.ldf” in your source database’s DATA directory. This directory should be in program files>microsoft sql server>[your sql instance name]>mssql>data.
  3. Paste those 2 files into the same directory on your destination computer. Make sure to copy the existing files to a backup folder before you overwrite!.
  4. Restart the service in SQL Server Configuration Manager, and that’s it. You can restart SambaPOS now and see your new database.

Now, this is just a method that I was able to figure out in my inexperience, and it may not be best practice. Somebody please let me know if there’s a better way to migrate/import/export databases. Also, this only copies the entire database, and I’d like to find a way to only migrate specific tables between SambaPOS instances (i’d really just like to transfer inventory items, recipes, products etc.); I haven’t been successful with the import/export tools in SQL Server Management Studio. If anybody can help with this, I’d love to know.

Thanks!
Matt

I would suggest you Use Database Backup Module

Here’s my Workflow and it works just fine

  1. I have 2 sambapos machines, one is a operational-version at the restaurant. Another one is for development/experiment purpose stored in my personal laptop that i always carried around
  2. I used Database Backup Module to automatically backup my operational-version database to dropbox after the work period ended. This allows me to Restore(Import Database) operational-version database into my laptop for experiment/development purpose.
  3. Once i’m happy with what I modified, I manually backup the database from my laptop and restore the database on the machine at workplace on the following day.

hope that helps

Thanks @fickle_123. I started off trying to use the backup module but ran into a problem using it on 2 different databases (see below). That’s what set me on this work-around path. How did you end up getting it to work on different computers?

edit: I just realized you were a part of the thread below, so you’re familiar with my module problem :slight_smile:

I use SQL Server Management Studio to Backup/Restore the Database. This would be the preferred method, if you are not using the DB Module provided by @emre.

I would be careful when doing Backup and Restore of individual Tables, since we are not fully aware of the relationship between one table and another. That is, if you add or remove anything (i.e. a Product and Menu Item) using SambaPOS, you won’t know how many Tables actually get altered in the Database, due to relationships and/or constraints within those table(s).

Backup creates a .bak file that you can ZIP and transfer wherever you want, then unZIP on the target machine and do the Restore.

Backup:









Restore:








@QMcKay That is a much more elegant solution, thanks! I’m being forced to dig into SQL management on the fly and I’m learning as I go.

Good point about not knowing the implications of manually modifying individual tables. @emre, would it cause problems if I were to build my InventoryItems table, Recipes table, and MenuItems table manually in excel and import them into the database with management studio, circumventing SambaPOS? I’m trying to find a more efficient way to enter thousands of items other than the POS GUI. I’ve imported 20 rows of inventory items this way, and they appear in SambaPOS normally; I just don’t know if I’ll be disrupting references between tables within SambaPOS.

@steet81 if you properly build your data from database side that should work. However since SambaPOS uses an ORM tool inside I even don’t have an idea about what database tables we have or how data stored inside.

Thanks @emre. I’m going to try to do my whole product/inventory build from the database side. I’ll report back when I get it up and running.

I wrote a bulk importer for V3 a while back http://forum2.sambapos.org/index.php/topic,1591.0.html.
It should work on V4.

Thanks @JohnS. I saw your thread a couple of days ago, but I couldn’t figure out how to download your attachment. Maybe I’m totally missing it, but I don’t see a download link on any post on this forum that shows the paper clip attachment icon (I’m new here). But the main reason I didn’t pursue your importer just yet is that I figured I needed to roll up my sleeves and get into SQL to teach myself the hard way. I would like to check out your importer though if you can help me get the file.

Thanks @JohnS. I just opened load_products.php to take a look and… wow, you’ve put a lot of work into this! I’m not even very familiar with SQL scripting, much less using php. I’m doing all my database editing with the import/export tool in SSMS and Excel, and I’m starting to see how the tables interact with each other not-so-simply in some instances.

@steet81, I have just updated the scripts as I found issues running on V4 databases.

@JohnS, awesome, thanks for making the changes so quickly! I’ve done all the steps, but I’m getting an error when I try to run the php file (screenshot below). I’m trying to track down the error, and I think it might be the connection string on line 62. I’m running SQL Server 2012, and I’m not sure which Native Client version was installed with that.

edit: I was able to get past the line 62 error by changing the Native Client version number to 11.0, but I just encountered about 40 errors after that (below). Any suggestions?

Looks like you didn’t config the SQL connection properly in the script.

Can you post your Data connection string from SambaPOS and also the SQL settings in the load_products.php script

from load_products.php:

$myServer = “LOCALHOST\SAMBAPOS3”;
$myUser = “sa”;
$myPass = “samba”;
$myDB = “SambaPOS4”;

from sambaPOS:

Data source=LOCALHOST\SAMBAPOS3; User Id=sa; Password=samba

Thanks,
Matt

And the SambaPOS4 database exists for SQL Instance SAMBAPOS3 ?

I’m running SQL Express 2012 without any issues. The errors are from the fact that the script cannot connect to the database.
You running it on the same computer as SQL Express ?

Yeah I didn’t stay consistent with my naming. I’m running everything locally.

I’m able to connect now. I should have tried this at the beginning, but I cleared the inventory, products, menus etc. from my database, and I was able to connect and run the php file.

side note: To clear the database, I had to run your “DELETE V3 Prod Inv Recipe MenuItems.sql” file in my own query cause I was getting errors with the batch file.

All of my items seem to have populated correctly when in SambaPOS, but I did receive some errors in the web browser after running the php (cap below). Maybe I can ignore the errors, since the POS seems to be running correctly so far.

Can you provide a screen shot if the error in the batch file please

I just got the batch file to run. I didn’t notice that line 8 of “DELETE V3 All Transactions.bat” says “localhost\SAMBAPOS” instead of “localhost\SAMBAPOS3”. The database is also named import, which I changed to SAMBAPOS4 for my instance.

Maybe line 8 of the batch file should say: SQLCMD -S localhost[SQL INSTANCE NAME] -d [DATABASE NAME] -i V3_del_Prod_Inv_Recipe_MenuItems.sql

and you could include a line in your tutorial to change the [SQL INSTANCE NAME] and [DATABASE NAME] in the batch file.

Thanks for all the help btw!!
Matt