Backup Database using SQL Query

In the last post, we have backed up the database using the SQL Server Management Studio.

Now we are going to do the same using SQL Query.

If you want to extract the Query that the SQL Server is using when backing up a database, it is simple.

Right Click on the Database, –> Tasks –> Backup. This will open the Backup database window.

On the top of Back up database window, Click on the Script button and select option, “Script Action to New Query Window”

 

This will give the SQL Query to backup that database in the new query window.

BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'D:\SQL DB\BACKUP\AdventureWorks2008R2.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'AdventureWorks2008R2-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

Give the Path and Name for the backup in the query and Run this query. This will take the backup of that database in that path.

Advertisement

BackUp a SQL Server DataBase

Now we are going to learn how to backup a database in SQL Server 2008R2.

First why we need a backup:

Before doing any major operations in the database, it is always a good practice to backup the database.

If something goes wrong in the database, you can simple restore the backup that you have taken and you can able to undo the database easily to a earlier stage.

Step 1: Open SQL Server Management Studio. Right Click on the Database. Select ‘Tasks’. Select ‘Backup…’

Step 2: Now the BackUp Database window opens and Select the Source Database. Select the Recovery model as ‘FULL”. In the destination Text area, Select Backup to Disk. Click on ‘Add’ button and choose the directory and file name that you want to have your backup. Example: “C:\SQLDB\AdventureWOrks20008R2.bak”

“.bak” is the file format for SQL Server database backup files.

Step 3: Click on the “Options” tab in Back Up Database window and mostly I will leave everything to default settings here.

Step 4: Click OK. You should get a prompt like below after the database is successfully backed up.

Now you can go and see the “.bak” backup file in the directory you have chosen. Thats it. You have backed up a database using SQL Server.

Running SQL Scripts from Command Line

Now we are going to learn how to run SQL Queries from Command Line
The SQLCMD utility lets you enter queries, procedures, and script files at the command prompt.
TO simple run a query and get the ouptput in command line, use the following statement.

sqlcmd -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE 'Whi%';"

Where
-d refers to the database that we are going to run our query
-q refers to the query which we are going to execute
The above statement will use windows authentication to run the query.
If you want to run the Query on a different server, using SQL Server credentials, then use the following command.

sqlcmd -S %Server% -U %UserName% -P %PassWord% -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

Where
-S refers to the database server
-U refers to the Username
-P refers to the password

sqlcmd -S MyServer -U sa -P password -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

sqlcmd -S localhost -U sa -P password -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

sqlcmd -S . -U sa -P password -d AdventureWorks2012 -q "SELECT FirstName, LastName FROM Person.Person"

Note:
-q will start the SQLCMD mode but does not exit sqlcmd when the query has finished running.
So in that case, if you want to exit the sqlcmd mode after running, use -Q [Caps Q]

sqlcmd -S MyServer -U sa -P password -d AdventureWorks2012 -Q "SELECT FirstName, LastName FROM Person.Person"

To run a Procedure/Sql Scripts from a .SQL File.
Now lets see how to run a storedprocedure/sql scripts stored in a file.
To do this, use the following command.

sqlcmd -S %Server% -U %UserName% -P %PassWord% -d %DataBase% -i "C:\SQLScripts\MySQLScript.sql" -I

sqlcmd -S %Server% -U %UserName% -P %PassWord% -d %DataBase% -i "C:\SQLScripts\MyProcedure.sql" -I

Where
-I
refers to QUOTED_IDENTIFIER in SQL

SET QUOTED_IDENTIFIER ON

Example:

sqlcmd -S MyServer -U sa -P password -d AdventureWorks2012 -i "C:\SQLScripts\MySQLScript.sql" -I

sqlcmd -S localhost -U sa -P password -d AdventureWorks2012 -i "C:\SQLScripts\MySQLScript.sql" -I

sqlcmd -S . -U sa -P password -d AdventureWorks2012 -i "C:\SQLScripts\MyProcedure.sql" -I

Install Fitnesse as Windows Service

As explained earlier, we can use the following command to run Fitnesse from Command Line.

java -jar fitnesse-standalone.jar -p 8082

Even if can create the above mentioned command as a Batch file (.bat) and run it whenever you want to start the Fitnesse tool.

Sometimes if we want to deploy all our wiki/test pages to a server, then running from batch file is not
a better option. So we can install the Fitnesse as a windows service.

So if you want to run this Fitnesse as Windows Service, then we can use the following steps.

There is a utility called Yet Another Java Service Wrapper – YAJSW
More details here: http://yajsw.sourceforge.net/

First download YAJSW from http://sourceforge.net/projects/yajsw/files/
Unzip the YAJSW in a directory.

I did it in the same directory where the Batch file is there to run the Fitnesse.

Check whether you have installed Java 1.5 or greater.
Start the Fitnesse Jar by using the batch file you like to wrap.
Check the process id of java.exe. NOTE: we need the pid of the application not the batch file which started the application.
Goto “yajsw/bat” directory and execute

genConfig.bat pid

Example: if Process ID for java.exe is 383, then

genConfig.bat 383

This generates the file “yajsw/conf/wrapper.conf” which is the configuration file for wrapping Fitnesse
Stop Fitnesse application
Open “conf/wrapper.conf” with a text editor. I always prefer notepad++ than notepad.
Update the following parameters in that Config value to the value mentioned below.

# Title to use when running as a console
wrapper.console.title=FITNESSE

#********************************************************************
# Wrapper Windows Service and Posix Daemon Properties
#********************************************************************
# Name of the service
wrapper.ntservice.name=FITNESSE

# Display name of the service
wrapper.ntservice.displayname=FITNESSE

# Description of the service
wrapper.ntservice.description=QA Acceptance Testing Framework

Save it

Execute your wrapped application running the batch file runConsole.bat under “yajsw/bat” directory.
check that Fitnesse is running bu opening it in the browser. http://localhost:8082/
To Install the application as service call installService.bat under “yajsw/bat” directory.
To start the service: startService.bat under “yajsw/bat” directory.

Goto services.msc and search for Fitnesse. There should be a services created for “Fitnesse”

Now open your browser and hit the URL http://localhost:8082/
This should navigate you to the FrontPage of Fitnesse

To stop the service: stopService.bat under “yajsw/bat” directory.
To uninstall the service: uninstallService.bat under “yajsw/bat” directory.

The above mentioned steps are explained in a Video Tutorial here: https://www.youtube.com/watch?v=gX9tGInrIvg

Fitnesse

Fitnesse is a Acceptance Testing Framework and also a Wiki Web Server
The good things is it is an Open Source Project.
You can create tests by adding Test Pages and also run them.
We can also consolidate the Test Pages into a Test Suite Page.

Getting Started:
Download Fitnesse jar from http://www.fitnesse.org/FitNesseDownload

To run the fitnesse use the following command.

java -jar fitnesse-standalone.jar -p 8082

Now open your browser and hit the URL http://localhost:8082/
This should navigate you to the FrontPage of Fitnesse

Delete a Windows Service

To delete a windows service running on your machine, use the following command in the command Prompt.

sc delete Servicename

For example, I am running Fitnesse –  Acceptance testing Tool as a service in my machine.

If I want to remove this windows service from my machine,

sc delete Fitnesse

SQL Search

SQL Search – quickly find SQL in SSMS

SQL Search is an add-in for SQL Server Management Studio that lets you quickly search for SQL across your databases.

SQL Search is currently a free tool, but we might charge for it in the future. If we do, the free version of SQL Search will still work.

 Why use SQL Search?

• Impact Analysis
  You want to rename one of your table columns but aren’t sure what stored procedures reference it. Using SQL Search, you can search for the column name and find all the stored procedures where it is used.
• Work faster
  Finding anything in the SSMS object tree requires a lot of clicking. Using SQL Search, you can press the shortcut combo, start typing the name, and jump right there.
• Make your life easier
  You need to find stored procedures you’ve not yet finished writing. Using SQL Search, you can search for stored procedures containing the text ‘TODO’.
• Increase efficiency, reduce errors
  You are a DBA, and developers keep using ‘SELECT *’ in their views and stored procedures. You want to find all these and replace them with a correct list of columns to improve performance and prevent future bugs. Using SQL Search, you can look for ‘SELECT *’ in the text of stored procedures and views.

Integration with SQL Server Management Studio

Select the Scope for your search

Results are returned as we type

And more important it is free from Red Gate. Download it here 

http://www.red-gate.com/products/sql-development/sql-search/download

 

SQL Complete

dbForge SQL Complete is a useful add-in for Microsoft SQL Server Management Studio and Microsoft Visual Studio. Free and advanced paid editions of the SQL code formatter offer powerful autocompletion and formatting of T-SQL code that replaces native Microsoft T-SQL Intellisense. –
See more at: http://www.devart.com/dbforge/sql/sqlcomplete/#sthash.D3NerdlZ.dpuf

This also helps you to Format your SQL Queries in a good look and feel structure.

Synthetic Data

This is a recent evloving practice in IT industry. Mostly related to testing the product/project.

For most of our testing, we may use copy of production data/de identified data for that product.

So what is a de-identified data:
It snothing but Masking or stripping off the sensitive areas of data that actually revelas the source of data.

More information here:
http://en.wikipedia.org/wiki/De-identification

So is that the synthetic data is an better alternative to Production/De-identified data? – Yes

Using the Production data for testing might reveal the sensitive/secured information to the outside world.
This is not a good practice.

Also De identifying data should be properly done. It should not expose any form of real information outside the members/clients environment.

For example people who all are working in HealthCare domain should know about the HIPAA – Health insurance Portability and Accountability Act.
It is like protecting the patient/members information. More details here: http://www.hhs.gov/ocr/privacy/

So the De identifying process should be done carefully such that it should not expose any form of sensitive data to the real world.
So instead of testing the application using Production/Data or De-identified data, synthetic data/artificial test data is always a good alternative.

Also we can easily attach a Expected Result for the Synthetic Data.

For example consider this scenario: Employees Appraisal Review Process:

This is a process, which has some business logics written to do some back end calculations and arrive the rating for all the employees.

I will create my own Employee called Employee_1_Test_John_Rating-3.5
and in the subsequent tables, I will enter the records that affects that employee performance to be derived as 3.5

Now I will run the backend job created by my developer and will see whehter the EMployee that I have created is getting 3.5 as rating in the
actual result or not.

By attaching the Expected Result to all the data that we are creating, we can easily compare the application actuals.
Otherwise we have to create the backend query with same logics that the developer has created to verify the application results.

This is all about my usage of Synthetic Data to test my products. More generic details here: http://en.wikipedia.org/wiki/Synthetic_data

RoboCopy in Windows

Robocopy (Robust File Copy) is a command-line file copy utility.

To use this utility simple type the following command in the command prompt.

Robocopy C:\SourceFolder C:\DestinationFolder

This will copy the contents of the source folder to destination folder.

To copy all contents including empty directories of SourceFolder to DestinationFolder:

Robocopy C:\SourceFolder C:\DestDir /E