How to know SQL Server Version using backup file?

Backup and restore these are common tasks while working with the database. But before restoring the database it is necessary that we should know about some information about backup file because it may be possible that we have created backup file in upper version of SQL Server and we are trying to restore it in lower version of SQL Server. In this is case database can not be restored.

So before facing this problem it is better to know about backup file means in which version it was created.

To get this information we can simply use RESTORE command with HEADERONLY. Check the bellow command -


RESTORE HEADERONLY 
         FROM DISK = N'd:\filename.bak' 

By passing the path of backup file you can run this command, you will get result like this-

 

By seeing SoftwareVersionMajor and SoftwareVersionMinor you can identify the version of the software.

To know about each column help follow this link-

http://technet.microsoft.com/en-us/library/ms178536.aspx

Here you will get description for each column.

How to update record without primary key?

If we have any primary key in table then it is very easy to update the records  in the database table, but generally we face problem when we don't have primary in table because on that time we can not use any particular column to update records because that column can contain duplicate values.

But there is work around for this problem, we can use” ROW_NUMBER()” function to generate identity value for each record and then based on that “ROW_NUMBER” we can delete record from the table.

In this article, I am explaining - How to update nth record of the table?
Or
How to update record without primary key?


For this I am using “ROW_NUMBER()” function in query, Here is syntax -

Syntax-

WITH Record AS (SELECT *, ROW_NUMBER() OVER (ORDER BY [ColumnName]) AS rownum FROM TableName where condition) update Record  set  col1=value ,col2=value where Record.rownum = value

Example-

WITH Record AS (SELECT *, ROW_NUMBER() OVER (ORDER BY EmpName) AS rownum FROM tab_Employee ) update Record  set  Age=25 ,Salary=45000 where Record.rownum = 1


In this example,   “ROW_NUMBER()” is a function which will generate sequence number for each record, here ordering will be based on EmpName column, you can change it.

Here "rownum" will be additional column in the result set, this will be used to update the record.

Here “Record “ is result set.


Try this example.


Thanks
 

How to delete nth row of the table in SQL Server?

If database table contains any primary key value then performing update and delete operations are not big task. But generally we face problem when we don’t have any primary key in table, on that case we can’t target to any particular column for deleting or updating records because that column can contain duplicate values.

In this article, I am explaining –

How to delete record based on row number? 
Or
How to delete nth row of the table?           
Or
How to delete record without primary key?           
 
                             
To perform this operation we have to use ROW_NUMBER() function of SQL Server. This function will help us to get nth row which we want to delete from table.

ROW_NUMBER()- 

ROW_NUMBER() is function in SQL Server which return sequential number for each record in result set. This returned number can be used as an identity for each record which is present in result set. When we don’t have any primary key in table then it is very useful to identify the record in result set. Using this sequential number we can perform operation like delete and update even we don’t have primary key for table.

Here is syntax to delete nth row from the table-

Syntax-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (Column_Name))
        FROM    Table_ame
        Where Condition
        ) AS Record
WHERE   Record.rowno = RowIndex

Here RowIndex is an index of the row which you want to delete from table. Here inner query will return result set including sequential number for each record and this will be ordered based on column_name which will be passed in ORDER BY clause. Finally outer query will perform delete operation based on given condition . 

Example-

Suppose if we want to delete 5th row of the Employee table then query will be like this-

DELETE  Record
FROM    (
        SELECT  rowno = ROW_NUMBER() OVER (ORDER BY (EmpName))
        FROM    Employee
        ) AS Record
WHERE   Record.rowno = 5

In above query, First inner query will be executed, which will generate result set over Employee table including row number over EmpName. Finally outer query will perform delete operation over result of inner query with given condition-

WHERE   Record.rowno = 5

This condition will detect 5th row from inner result and delete record from the table. In this example I didn't put any condition for inner query but if we have requirement then we have put where condition also to get result set like I have written in given syntax.


Thanks