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”

Image

 

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.

Advertisements

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…’

Image

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.

Image

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

Image

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

Image

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

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

Image

Select the Scope for your searchImage

Results are returned as we type

Image

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

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