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!

Comments

Popular posts from this blog

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...

Reasons why you don't apply to some Companies

Just a note, this is just a joke. So for some companies, I would ask you to evaluate me first not base your judgement for hiring me in this post. :) Recently, I planned on getting another job. This time with programming languages I'm not familiar with. Just to get my hands on. Something like Python or Ruby, or JS and NoSQL. You know what I mean. Then came, job posts at some Ads. Recruiters really think they are smart. They're not even reviewing what they're posting and not getting consultation from experts offering a job. For example SEO Experts (20k to 30k) with GREAT “DAYTIME” CAREER OPPORTUNITIES A NEW COMPANY soon to expand OPERATIONS in CEBU!!! I'm sure 30k is not that great. And what's with the DAYTIME? Also Perfect Worklife Balance: “Daytime Work” and Guaranteed 5 day work week only Easy to Manage Accounts Excellent Compensation based on expertise, we are only looking for top talent and pay accordingly Performance and Attendance bonuses...

Efficient Cleanup of Temporary Database Tables in SQL Server

 In database management, it's not uncommon to create temporary tables for various purposes, such as storing intermediate results or performing complex operations. However, managing these temporary tables and ensuring they don't clutter your database can be a challenge. In this blog post, we'll explore a useful SQL query that simplifies the cleanup process for temporary tables in SQL Server. The Challenge of Temporary Tables Temporary tables are often created dynamically during database operations, especially in scenarios involving complex queries or data manipulation tasks. While temporary tables serve their purpose during runtime, they can accumulate over time and lead to database clutter if not properly managed. Manual deletion of these tables can be time-consuming and error-prone, especially in databases with numerous temporary tables. The Solution: Dynamic Cleanup Query To address this challenge, we can leverage a dynamic SQL query to automate the cleanup of temporary t...