DB_NAME() System Function in SQL Server

In this blog, let’s take a look at a SQL Server System function, DB_NAME() .

This function is pretty handy when writing scripts that need you to identify the the Database name based on Database ID.

DB_NAME()

DB_NAME() function accepts an optional integer parameter (database_id) and returns the database name of that database_id, if no parameter is mentioned it returns the database name of the current database in whose context the script session is being executed. Let’s look at examples to understand it.

In the following picure, the function DB_NAME() returns the name of the database whose database_id is 1. Since the parameter value is passed on it returns the database name of that database_id, even though the query is running in the database SampleDataBase.

In the next example we look at how this function behaves when the optional parameter is not passed.

 

The result in this query is the database name in which the  query is executed. It returns the database name under whose contect the query is run, when there is a requirement to capture the database name through script, this can be used.

You may also want to take a look at this blog which explains about renaming the Database and the Database objects using T-SQL command. http://learnsqlwithbru.com/2010/03/01/renaming-database-and-database-objects-using-t-sql/

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Retrieving data from a Database Snapshots in SQL Server

I wrote a blog earlier, on Database Snapshots which explained about Database Snapshots, steps to create Database Snapshots and some points to remember when working with them. Before continuing reading this blog, I would recommend you read that blog here… Introduction to Database Snapshots in SQL Server

In this blog we shall look at a simple task of restoring data from a Database Snapshots in case if a table is deleted accidentally.

I would recommend you to use the scripts that are available in the earlier blog mentioned above to create the database snapshot and the tables used in this blog. We start this process by dropping a table and assume that, this has happened as an unforeseen event (accidentally dropping a table, in other words, dropping a table instead of dropping an actual one).

Once that table is dropped, our next step is to create the same table and bring in the data from one of the most recent Database Snapshots. In order to do this I am using the SELECT INTO statement as shown below.

To confirm whether or not the table has got the data back, we run the SELECT command on the table we created and populated data, in the previous step.

In this way Database Snapshots can also be used an alternate mechanism of Disaster Recovery for unforeseen events such as accidental dropping, truncating or updating of data.

*** Note: You cannot retrieve entire data from a database snapshot table, since it contains only the data in the table at the point of creation of the database snapshot. Any new data that has been inserted, updated, modified or deleted after the creation of the database snapshot cannot be retrieved.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty