Convert TIMESTAMP in DATE data type format in TERADATA

Many times we need to compare two column and we succeed most of the time since we do have same data type and same values. But when it comes to different data types then it can be more difficult especially with date data type. When we need to compare date data type with timestamp data type. We can only compare this only when we convert the timestamp values in date data type. We can convert TIMESTAMP data type in DATE data type by using CAST in TERADATA.

Below is the example:-

Your Timestamp values is :- '2015-01-01 12:00:00'

dt_update   ----having date data type from EMP table.
updated_date_time ------having timestamp data type from EMPLOYEE table.

SELECT A.DT_UPDATE,CAST(B.UPDATED_DATE AS DATE FORMAT 'YYYY-MM-DD') AS UPDATED_DATE FROM EMP A,EMPLOYEE B
WHERE A.DT_UPDATE=B.UPDATED_DATE;

OR

SELECT A.DT_UPDATE,CAST(B.UPDATED_DATE AS DATE FORMAT 'YYYY-MM-DD') AS UPDATED_DATE FROM EMP A,EMPLOYEE B
WHERE A.DT_UPDATE=CAST(B.UPDATED_DATE AS DATE FORMAT 'YYYY-MM-DD');

Free Books Legally

Click Here

SYS_CALENDAR.CALENDAR view in Teradata

SYS_CALENDAR.CALENDAR view in Teradata provides the information about the calendar. For example you need to find out total number of day's in a month without Saturday and Sunday. It alos provides you month, quarter and other information.

How to restore SQL database

Restoring a DB is as simple, simply enter the following command on the destination SQL server (if not the same one).  In this example we’re assuming the DB is called “daytona” on the local server where the SQL DB needs to be restored to.  The “WITH REPLACE” option over-writes the existing “daytona” database:

sqlcmd -E -S localhost\RFCASSETMGR –Q “RESTORE DATABASE [daytona] FROM DISK=’c:\sqlbak.bak’ WITH REPLACE"

 If during the Backup or Restore process an “Operating system error 5(Access is denied.)” error occurs, you need to modify the Windows Service (SQL Server (RFCASSETMGR)) to run as a “Local System account.”

SQL Database Backup using SQL commands

New, Let's see how we can take backup by using SQL commands.

SQLCMD is used to connect to SQL Server management studio from command prompt.

OSQL is used to connect to SQL Server management studio from command prompt below SQL Server 2005 version.

You can use windows .batch file to schedule the job daily to backup the database.

Below is the simple command which can be use to backup the database from consol after connecting to the SQL Server:-

BACKUP DATABASE TEST TO DISK='F:\Chanchal\Sql_Database_Backup\TEST_DB.BAK';

GO

Note:-  You would always need to type GO keyword otherwise your query won't get executed.

Below is the command which can be used before connecting to the SQL Server database:-

SQLCMD -E -S TEST_SERVER –Q "BACKUP DATABASE TEST TO DISK=’F:\CHANCHAL\SQL_DATABASE_BACKUP.BAK'"

Below is the screen shot for above command:-

 Above command can be schedule in windows bat file and can be call on particular time so the automatic backup happens.

By using above commands you can backup your database on MEDIA as well.

How to backup SQL Database

In day to day business, SQL DBA's have to take daily backup of their databases. Without taking backup their job can be hectic. So, in this tutorial we will find out different ways to take Sql Server Database Backup.

We can take Sql Server Database backup manually or we can schedule it so the databases gets backup automatically.  In this tutorial we will find out both the ways to take backup manually as well as schedule the same.

First let's try to take the backup manually as shown below:-

Before we starts, we need to be aware of below points:-

1.       Backups that are created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.

2.       To backup database you need to have administrative privileges.

3.       When you specify a back up task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUP script by clicking the Script button and selecting a script destination.

The most important option that you need to know about SQLCMD are as follows:-

Note:- SQLCMD options are case-sensitive.

·       -S <sql_server_name>:-  the name of the SQL Server, including instance, if applicable. You may use "." (without quotes) if you are running SQLCMD on the same machine as SQL Server.

·       -d <database_name>:-  the name of the database on which the operation is to be performed. For example -d Test.

·       -U <user_name>:-  the SQL Server user account under which to run the specified command.

·       -P <password>:- the password associated with the specified user account.

·       -E :- use NT authentication to interact with SQL Server

·       -Q "<SQL-command>":- the command to issue to SQL Server.

·       -i <file_of_SQL_commands>:-  a file containing one or more commands to issue to SQL Server.

SQLCMD can also operate interactively. If you specify only the server, database, user name, and password, you will be presented with a prompt allowing you to enter and execute commands one at a time. The command "exit" exists interactive mode.

For more information about other options, you can type "SQLCMD -?" for help.

  

Let's start to back up a database:-

1.       Connect to the appropriate instance of the Microsoft SQL Server Database Engine, you would see the list of objects on the left hand side:-

2.       Expand Databases, you will find out many databases:-

 

3.       Right-click the database which you want to backup, go to Tasks, and then click Back Up. The Back Up Database dialog box appears. In my case, I am backing up "Test" database:-

 

Now we are in the General tab

4.       In the Database list box, verify the database name. You can optionally select a different database from the list.

 

5.       You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE).

Here:-

Full:- 

A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored.

Bulk_logged:-

The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.

Simple:-

The simple recovery model provides the simplest form of backup and restore. This recovery model supports both database backups and file backups, but does not support log backups. Transaction log data is backed up only with the associated user data. The absence of log backups simplifies managing backup and restore. . However, a database can be restored only to the end of the most recent backup.

6.       In the Backup type list box, select Full.

Note:-  After creating a full database backup, you can create a differential database backup.

7.       For Backup component, click Database:-

8.       Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set:-

 9.       Optionally, in the Description text box, enter a description of the backup set:-

10.       Choose the type of backup destination by clicking Disk or Tape. Click on Disk and now hit on ADD button "Select Backup Destination" Dialog box will open:-

 

11.       Click on the Brows button and select the folder where you want to save the backup:-

In my case, I have saved the backup file on F drive in Chanchal\Sql_Database_Backup Folder with the name "Test_Full".  

12.       You have provided path and name of the backup file now click on OK.

Above dialog box will show you the path that you have selected.

13.       Now, again click on OK. You can see the path on Destination:-

 

14.       Now go to the "Option" tab. Select the "Overwrite Media" option. In my case I am Choosing "Overwrite All Existing Backup sets":-

15.       Now, Click on OK. Below message will appear:-

16.       Verify that the backup file has generated:-

You have successfully backed up the database. In case if you need to restore it you can perform restore database action.

Above we have performed manual way to backup the database. Now let's see how we can back up the database by using commands which then we can schedule to automate daily backup.