Schedule Data Backup from Production to Development

OR
Auto synchronize Data from Production to Development using Script Daily
OR
Refresh data from Production to Development Server.

We mostly seen a situation where we want to synchronize data from Production to Development or QA Server on demand request, daily or weekly basis.

We have different tools to achieve it.

1. Red Gate Data Synchronization
2. Data Synchronize using SSDT

I come to a situation where my client doesn’t have a license software for SQL Server so we have installed express edition which doesn’t have SSDT installed with it.

In such situation, I thought to create dynamic procedure sc.cp_data_synch which will synchronize data from Production to Development Server based on table definition in Development Server.

Below are parameters for the procedure sc.cp_data_synch.

1. @p_SourceServerName
   1. Server Name of Production Server from where we want to synchronize data.
2. @p_SourceUserName
   1. User Name of Production Server.
3. @p_SourcePassword
   1. Password of Production Server
4. @p_SourceDatabaseName
   1. Database Name of Production Server

Below is sample execution plan.

exec [sc].[cp_data_synch]
@p_SourceServerName = 'Source Server Name',
@p_SourceUserName = 'Source User Name',
@p_SourcePassword = 'Source Password',
@p_SourceDatabaseName = 'Source Database Name'

Batch File

Now, we will see how to create batch file which will execute above script.

Below is command which is used to execute above script.

SQLCMD -S [Server Name] -U [User Name] -P [Password] -Q "EXEC Asif.dbo.cp_data_synch"

You can get more details about SQLCMD from below link.
http://technet.microsoft.com/en-us/library/ms162773.aspx
 Below are details of above parameters.

1. [Server Name] : You can get server name from you SQL Server Management Studio (SSMS) using below query

Query:
Select @@servername
2. [User Name] (Optional): If you want to connect SQL Server using SQL Authentication then you can define your user name here. Else it will connect using Windows authentication.

3. [Password] (Optional) : You can define SQL Server User Name Password here.

Scheduler

Now, we will see how to create windows "Task Scheduler" which will execute above batch file daily.

To do that, we need to follow below steps.

1. Go to Start -> Control Panel -> Administrative Tools -> Task Scheduler

2. Click on "Create Basic Task".

3. Enter Name and Description of Task and Click Next.

4. Choose appropriate schedule type and Click Next. I have choose "Daily".

5. Choose appropriate start time and recurring based on Schedule Type selected in last step. I have choose 9 AM for every day.

6. Choose action which you want to perform and Click Next. We have choose "Start a program" because we want to execute batch file for this task.

7. Browse batch file location from where you want to execute batch file for this task.

8. Below is task which is created in Windows Task Scheduler.

Below are scripts to download:

1. sc.cp_data_synch.sql

Unable to make the session state request to the session state server. Please ensure that the ASP.NET State service is started and that the client and server ports are the same. If the server is on a remote machine, please ensure that it accepts remote requests by checking the value of HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\aspnet_state\Parameters\AllowRemoteConnection. If the server is on the local machine, and if the before mentioned registry value does not exist or is set to 0, then the state server connection string must use either 'localhost' or '127.0.0.1' as the server name.

To fix above error, I followed below steps.

1) Open Run Command and type “Services.msc”

2) Right click on ASP.NET State Service and Start.

How to prefix 0 to make same length string for column.

1. There’s a table with below data.

Write a query which will give below output. 

Query:
declare @employee as table(
id int
)
insert into @employee values(1)
insert into @employee values(20)
insert into @employee values(300)
insert into @employee values(4000)
insert into @employee values(50000)
insert into @employee values(600000)

select * from @employee

select replicate('0',6-len(id)) + cast(id as varchar) from @employee

SQL Server Theoretical Interview Question

Theoretical Question:

1. Is CDC asynchronous?

Ans: Yes

2. Is it possible to use having function without group by? Give example

Ans: Yes
Query:

declare @input as table

(
       column_name varchar(100)
)

insert into @input values('AAA')
insert into @input values('BBB')
insert into @input values('CCC')

select
       max(column_name)
from
       @input
having
       max(column_name) >= 'AAA'

Output:

3. What are the constraints in SQL Server? Give me the name of it.
Ans: Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. The query optimizer also uses constraint definitions to build high-performance query execution plans.
a)   NOT NULL
b)   CHECK
c)   UNIQUE
d)   PRIMARY KEY
   a.   FOREIGN KEY
   b.   NO ACTION
   c.    CASCADE
   d.   SET NULL
   e.   SET DEFAULT

4. What’s difference between Function and Stored Procedure?
Ans:
·  Procedure can return zero or n values whereas function can return one value which is mandatory.
·  Procedures can have input/output parameters for it whereas functions can have only input parameters.
·  Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
·  Functions can be called from procedure whereas procedures cannot be called from function.
·  Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
·  We can go for transaction management in procedure whereas we can't go in function.
·  Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
·  UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
·  UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
·  Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

5. Types of parameters in Procedure
Ans:
·  Input and
·  Output.

6. What’s difference between Primary Key and Foreign Key?
Ans: A primary key is a column which uniquely identifies the records in a table. In a broad sense, a primary key is the mixture of a unique key and an index: A collumn with a primary key is indexed to deliver a faster query, and doesn't allow duplicate values to ensure specific data.

A foreign key is a column (the child column) in a table which has a corresponding relationship and a dependency on another column (the parent collumn) that is usually in a different table. Parent columns can have multiple child columns, but a child collumn can only have one parent column.

7. What are the rank functions in SQL Server?
Ans: Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
Transact-SQL provides the following ranking functions:
·  RANK
·  DENSE_RANK
·  NTILE
·  ROW_NUMBER

8. What is CTE and use of it?
Ans: A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:
·  Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
·  Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
·  Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
·  Reference the resulting table multiple times in the same statement.

9. What are the types of trigger and syntax for them?
Ans: A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
·  DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
·  DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
·  Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.

10. What is difference between declare variable and temporary table. Which is best and give scenario for that?
Ans:
·  Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
·  Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just includes the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.
·  Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features. So for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!
·  Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
·  You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
·  You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
·  Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
·  Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.
·  Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

11. What’s different type of indexes? Which is best of it? Give the scenario for that.
Ans:
Clustered: A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Nonclustered: A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

Unique: A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.

Both clustered and nonclustered indexes can be unique.

Index with included columns: A nonclustered index that is extended to include nonkey columns in addition to the key columns.

Full-text: A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.

Spatial: A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.

Filtered: An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

XML: A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.

12. What’s dynamic SQL query and how to execute it?
Ans: Dynamic SQL is query which is build up on fly. sp_executesql executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

13. What’s index seek and index scan?
Ans: Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

14. How to generate comma separated values for columns for a given table.
Ans: There are 2 ways
·  COALESCE
·  FOR XML PATH

15. There is a table employee which have one record for employee. I want to repeat same record 5 times. How to achieve that?
Ans: We can do this using CROSS APPLY.

Script to view Dependencies for Database in SQL Server

SQL Server is providing feature to view Dependencies in SQL Server. To view object dependencies, we are following below steps in SQL Server Management Studio.

1.       Right click the object and click on View Dependencies.

2.  In that, we have two options
a.  Objects that depend on our object.

b.      Objects on which our object depends

   

Now, we will try to understand, How SQL Server is fetching this details. To achieve that, I ran SQL Server Profiler and found scripts. I used these scripts and create procedure sc.cp_find_referencing_objects with below parameters.

1.       @p_find_referencing_objects:

a.       1 = to fetch objects that depend on our object.

b.      0 = to fetch Objects on which our object depends

2.       @p_object_id

a.       Object_id from sys.objects table

Now, we will try to use this procedure to view object dependencies.

a.       Objects that depend on dbo.cp_insert_error_log.

Query:

select object_id from sys.objects where name = 'cp_insert_error_log'

Output:

EXEC sc.cp_find_referencing_objects

       @p_find_referencing_objects = 1,

       @p_object_id = 293576084

Below are details of each variable.
Output:

b.      Objects on which dbo.cp_insert_error_log depends.

Query:

EXEC sc.cp_find_referencing_objects

       @p_find_referencing_objects = 0,

       @p_object_id = 293576084

Output:

Now, we will use above procedure and try to find dependencies for well knows objects like Table, Function, View and Procedure.

Query:

create table #tmpObject

(

       object_id int,

       object_name sysname collate database_default,

       object_schema sysname collate database_default,

       object_db sysname,

       object_svr sysname,

       object_type smallint,

       relative_id int,

       relative_name sysname collate database_default,

       relative_schema sysname collate database_default,

       relative_db sysname,

       relative_svr sysname,

       relative_type smallint,

       schema_bound bit,

       ptype int,

       pname sysname,

       pschema sysname

)

Declare object_cursor Cursor DYNAMIC

For

Select

       object_id

From

       sys.objects

Where

       type in (

              -- Table

              'U',

              -- Function

              'TF','FN','IF','FS','FT',

              -- View

              'V',

              -- Procedure

              'P','RF','PC'

       )

       and object_id not in(select relative_id from #tmpObject)

Order by

       type,

       name

Open object_cursor

Declare

       @v_object_id int

      

Fetch Next From object_cursor INTO @v_object_id

While (@@FETCH_STATUS <> -1)

Begin

       If not exists(select 1 from  #tmpObject where relative_id = @v_object_id)

       Begin

              -- Insert into temporary object

              Insert into #tmpObject(

                     object_id,

                     object_name,

                     object_schema,

                     object_db,

                     object_svr,

                     object_type,

                     relative_id,

                     relative_name,

                     relative_schema,

                     relative_db,

                     relative_svr,

                     relative_type,

                     schema_bound,

                     ptype,

                     pname,

                     pschema

              )

              -- Objects which are depeneded on it.

              EXEC sc.cp_find_referencing_objects

                     @p_find_referencing_objects = 0,

                     @p_object_id = @v_object_id

       End

       Fetch Next From object_cursor INTO @v_object_id

End

-- Close Cursor

Close object_cursor

-- Deallocate Cursor

Deallocate object_cursor

-- Delete for same object reference

Delete from #tmpObject where object_id = relative_id

-- Delete duplicate entries

Delete from T

from

(

       Select

              object_id,

              relative_id,

              row_number() over(partition by object_id, relative_id order by object_id, relative_id) rownumber

       from

              #tmpObject

)T

Where

       rownumber > 1

-- Select

Select

       relative_db,

       case relative_type

              when 3 then 'Table'

              when 0 then 'Function'

              when 2 then 'View'

              when 4 then 'Procedure'

              when 6 then 'Default object'

              when 7 then 'Rule'

              when 8 then 'Trigger'

              when 11 then 'Aggregate function (CLR)'

              when 12 then 'Synonym'

              when 13 then 'Sequence'

              when 101 then 'Type'

              when 102 then 'XML_SCHEMA_COLLECTION'

              when 103 then 'Type'

              when 1000 then 'Assembly'

              when 201 then 'Partition Scheme'

              when 202 then 'Partition Function'

              when 104 then 'Type'

              when 203 then '203'

              when 1001 then 'Unknown'

              when 204 then 'Plan Guide'

              else cast(relative_type as varchar)

       end relative_type,

       relative_schema,

       relative_name,

       object_db,

       case object_type

              when 3 then 'Table'

              when 0 then 'Function'

              when 2 then 'View'

              when 4 then 'Procedure'

              when 6 then 'Default object'

              when 7 then 'Rule'

              when 8 then 'Trigger'

              when 11 then 'Assembly'

              when 12 then 'Synonym'

              when 13 then 'Sequence'

              when 201 then 'Partition Scheme'

              else cast(object_type as varchar)

       end object_type,

       object_schema,

       object_name

From

       #tmpObject

Order by

       relative_db,

       relative_type,

       relative_schema,

       relative_name,

       object_type,

       object_schema,
       object_name,

-- Drop temporary objects

Drop table #tmpObject

Output:

Below are scripts to download:
1. Find referencing objects.sql