Skip to main content

How to Backup using SQL Server Data

 Sometimes Task->Generate Scripts takes too time to backup especially when you want to backup only selected tables in the database. This can be done easily with bcp, issuing the command in your power shell dumps the data in tab-delimited values into a file.

\> bcp "DATABASE.dbo.TABLE" out "C:\Downloads\TABLE.sql" -c -S "HOSTNAME" -U USERNAME -P PASSWORD

Hope this helps, this is very useful for migrating data and for seeders where some tables are only needed to be seeded into the database as dependent values for other tables. We are heavily using this technique for testing. As for reference, here's our use case

  • Dump data into .sql files
  • Seed using QueryFromFile
  • Run automation; this can insert new values to other tables based from the seed data from .sql file
  • Rollback new values
We need to rollback each time since we want to have data the same as it was so we can run each time and expect the same results for our testing. If not, there should be error somewhere or a bug introduced in the new code that we need to fix.

That's it for now. See you in the next blog. Take care everyone!

Labels:

Comments

Post a Comment

Popular posts from this blog

Restoring .dat data to MSSQL

This is just a document in case I forgot to restore data files to MSSQL. I had this problem before when a colleague gave me a .dat files to be restored to SQL server. I had a 2008 version of the server and had no trouble restoring it. The problem is, I forgot how I did it when I had a new PC and I upgraded it to 2012 SQL server. But this is a simple task (although I had a full day remembering how I did it and I had no luck finding the solution from google). First off, click restore, like so I had trouble with the database whether or not to add a new database and restore the file to it, or not. Simple answer is no need. With the next image you can see that the configuration is very simple. Just set it to device as the source and find the .mdf file. It automatically displays a suggestion for the database name and you're done. Click OK and wait a couple of minutes for SQL server to restore the data. [EDIT] In case you are restoring a backup file without an extension. You c...
Post a Comment
Read more

Python Hydrology

Recently I'm learning Python and encountered the Hydrology book. Had to upgrade some of python packages and some libraries needed to get working with the book. There is an instruction though about mostly using pip install packages need for the book. The problem is I can't get it work. It's missing some things so I did a little google and hopefully this is a compilation of the things I need to work with the examples of the book. First off, I updated the apt-get $ sudo apt-get update Next is I installed the pip package manager. I'm not too sure but I have Ubuntu 10 and it doesn't have the pip installed. So I installed using the command below. $ sudo apt-get install python-pip Now the development packages for python. $ sudo apt-get install python-dev Most of the examples uses the scientific number packages so I installed numpy and other packages I'm not quite aware of. It's just that some of the examples probably in the later part of the book use...
2 comments
Read more

Setting Up IIS on a Local Network: A Developer's Journey

Today, I embarked on a mission to set up an IIS server on our local network. The primary objective? To create local network versions of our applications for testing and development purposes. Additionally, we're in the process of setting up a CI/CD server to automate our workflows. However, like many journeys, this one had its share of hurdles. The initial hiccup arose when I realized that .NET was installed before IIS, leading to compatibility issues. Despite attempting to install IIS through the control panel and tweaking .NET components, the problem persisted. "Nada." After some trial and error, a breakthrough emerged. The solution? Executing dism as an Administrator in the C:\Windows\Microsoft.NET\Framework\v4.0.30319 directory: \> dism /online /enable-feature /featurename:IIS-ASPNET45 /all And just like that, the clouds parted, and the path forward became clear. With IIS up and running seamlessly, our developers rejoiced, and testing commenced with renewed vigor. H...
Post a Comment
Read more