May 5, 2014

An aggregate may not appear in the set list of an UPDATE statement sql server

If we will try to update a column of table with aggregate function:


UPDATE [tblActor]
     SET [RelativeScore] = (SELECT  SUM([tblActor].ntScore)
          FROM [tblActor] A

          WHERE A.vcActorCity = [tblActor].vcActorCity )

 

We may get error message something like:


Msg 157, Level 15, State 1, Line 2

An aggregate may not appear in the set list of an UPDATE statement.


Cause: We cannot use aggregate function in update statement as a sub query or join. 


Solution: Instead of sub query use Common table expression or derived table. For example:


--Using common table expression
;WITH cteAS(
     SELECT
          vcActorCity,
          SUM(ntScore) ntScoreSum FROM [tblActor]
     GROUP BY vcActorCity
)UPDATE [tblActor]
     SET [RelativeScore] = ntScoreSumFROM cteWHERE Cte.vcActorCity = [tblActor].vcActorCity


--Using derived table
UPDATE [tblActor]
     SET [RelativeScore] = ntScoreSum
FROM (
     SELECT
          vcActorCity,
          SUM(ntScore) ntScoreSum
     FROM [tblActor]
     GROUP BY vcActorCity) DT
WHERE DT.vcActorCity = [tblActor].vcActorCity

May 3, 2014

Recover accidentally updated data in sql server: Point in time restore with last old backup

Some time we update the data accidentally in the production server. We have last old backup but there is probability that some records has been inserted or deleted from the last back up time. Here we will discuss how to restore the data at specific poin of time.

Step 1:  I'm creating a database, say ExactHelp:

CREATE DATABASE ExactHelp

Step 2: I'm creating a table say tblUser and inserting some records into it:

USE ExactHelp

CREATE TABLE tblUser(
    ntUserID BIGINT PRIMARY KEY IDENTITY,
    vcUserName VARCHAR(200),
    vPassword VARCHAR(500),
    btActive BIT DEFAULT(0)
)

INSERT tblUser VALUES
    ('johnny_depp@exacthelp.com','sqlsecure',1),
    ('tom_hanks@exacthelp.com','sqlsecure',1),
    ('hahrukh_khan@exacthelp.com','sqlsecure',0),
    ('leonardo_di_caprio@exacthelp.com','sqlsecure',1),
    ('al_pacino@exacthelp.com','sqlsecure',1),
    ('jack_nicholson@exacthelp.com','secure01',1),
    ('kamal_haasan@exacthelp.com','secure01',0),
    ('tom_hiddleston@exacthelp.com','hiddleston',1)

3. Now table has following records:

SELECT * FROM tblUser

ntUserID
vcUserName
vPassword
btActive
1
johnny_depp@exacthelp.com
sqlsecure
1
2
tom_hanks@exacthelp.com
sqlsecure
1
3
hahrukh_khan@exacthelp.com
sqlsecure
0
4
leonardo_di_caprio@exacthelp.com
sqlsecure
1
5
al_pacino@exacthelp.com
sqlsecure
1
6
jack_nicholson@exacthelp.com
secure01
1
7
kamal_haasan@exacthelp.com
secure01
0
8
tom_hiddleston@exacthelp.com
hiddleston
1
  
Step 4: Now I'm going to create a full backup of the database ExactHelp:

--Data backup   
BACKUP DATABASE ExactHelp
TO DISK = N'F:\DbBackup\ExactHelp.bak'

--Log Backup
BACKUP LOG ExactHelp
TO DISK = N'F:\DbBackup\ExactHelp_Log.bak'

Step 5: Now I'm going to insert some more records in the table tblUser:

INSERT tblUser VALUES
    ('angelina_jolie@exacthelp.com','sqlsecure',1),
    ('charlize_theron@exacthelp.com','sqlsecure',1),
    ('marion_cotillard@exacthelp.com','sqlsecure',0),
    ('julia_roberts@exacthelp.com','sqlsecure',1)
   
Step 6: Now table tblUser has following records:

ntUserID
vcUserName
vPassword
btActive
1
johnny_depp@exacthelp.com
sqlsecure
1
2
tom_hanks@exacthelp.com
sqlsecure
1
3
hahrukh_khan@exacthelp.com
sqlsecure
0
4
leonardo_di_caprio@exacthelp.com
sqlsecure
1
5
al_pacino@exacthelp.com
sqlsecure
1
6
jack_nicholson@exacthelp.com
secure01
1
7
kamal_haasan@exacthelp.com
secure01
0
8
tom_hiddleston@exacthelp.com
hiddleston
1
9
angelina_jolie@exacthelp.com
sqlsecure
1
10
charlize_theron@exacthelp.com
sqlsecure
1
11
marion_cotillard@exacthelp.com
sqlsecure
0
12
julia_roberts@exacthelp.com
sqlsecure
1

Step 7: My client assigned me a task to execute this script:

UPDATE tblUser SET
    vcUserName = 'kate_winslet@mfs.com',
    vPassword = 'sqlsecure123',
    btActive = 1
WHERE ntUserID = 4

But accidentally I missed the WHERE cluase :( And executed it like this:

UPDATE tblUser SET
    vcUserName = 'kate_winslet@mfs.com',
    vPassword = 'sqlsecure123',
    btActive = 1

Step 8: Due to my bad luck table user has following records:

ntUserID
vcUserName
vPassword
btActive
1
kate_winslet@mfs.com
sqlsecure123
1
2
kate_winslet@mfs.com
sqlsecure123
1
3
kate_winslet@mfs.com
sqlsecure123
1
4
kate_winslet@mfs.com
sqlsecure123
1
5
kate_winslet@mfs.com
sqlsecure123
1
6
kate_winslet@mfs.com
sqlsecure123
1
7
kate_winslet@mfs.com
sqlsecure123
1
8
kate_winslet@mfs.com
sqlsecure123
1
9
kate_winslet@mfs.com
sqlsecure123
1
10
kate_winslet@mfs.com
sqlsecure123
1
11
kate_winslet@mfs.com
sqlsecure123
1
12
kate_winslet@mfs.com
sqlsecure123
1

Now I want to get back my old data otherwise he will kill me..

If I'll restore the the last backup file I will get data which was at step 3 but I need data at step 6. Good news is sql server support point in time backup :) Now I'm going to recover old records.

Step 9: Creating the transaction log backup:

BACKUP LOG ExactHelp
TO DISK = N'F:\DbBackup\ExactHelp_Log_1.bak'

Step 10: Finding the lsn number of accidental update operation. By this script we can get lsn number of the update operation:

SELECT DISTINCT
     L.[Begin Time] AS UpdateTime,
    
     CAST(CAST(CONVERT(VARBINARY,PARSENAME(REPLACE(L.[Current LSN],':','.'),3),2) AS INT) AS VARCHAR ) +
     RIGHT('0000000000' + CAST(CAST(CONVERT(VARBINARY,PARSENAME(REPLACE(L.[Current LSN],':','.'),2),2) AS INT) AS VARCHAR ),10) +
     RIGHT('00000' +CAST(CAST(CONVERT(VARBINARY,PARSENAME(REPLACE(L.[Current LSN],':','.'),1),2) AS INT) AS VARCHAR ),5) AS LnsNumber,

     L.[Transaction Name] [Type],
     ISNULL(PARSENAME(T.AllocUnitName,2),T.AllocUnitName) AS TableName,
     SUSER_SNAME(L.[Transaction SID]) UpdatedBy
FROM sys.fn_dblog(NULL,NULL) L INNER JOIN  sys.fn_dblog(NULL,NULL) T
ON LEFT(L.[Current LSN],17) = LEFT(T.[Current LSN],17)
WHERE L.[Transaction Name] = 'UPDATE'
     AND T.AllocUnitName LIKE '%tblUser%'

Output:

UpdateTime
LnsNumber
Type
TableName
UpdatedBy
2014/05/03 17:11:57:123
113000000016200001
UPDATE
tblUser
Ritesh-PC\Ritesh

We may get more than row in the output. You will have to choose correct one manually. In this example my update operation lsn number is: 113000000016200001

Step 11: Now I'm creating new database which is copy of database ExactHelp by using last backup file (I have created backup at step 4). New database name is ExactHelp_Copy:

--Data restore
RESTORE DATABASE ExactHelp_Copy
FROM  DISK = N'F:\DbBackup\ExactHelp.bak'
WITH NORECOVERY ,
MOVE 'ExactHelp'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\ExactHelp_Copy.mdf',
MOVE 'ExactHelp_Log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\ExactHelp_Copy_log.ldf'

--Log file restore
RESTORE DATABASE ExactHelp_Copy
FROM  DISK = N'F:\DbBackup\ExactHelp_Log.bak'
WITH NORECOVERY

Note: Now table tblUser has exectly same records which was at step 3. But we cannot fetch data now since table is in the state of not recover.

Step 12: In this step I'm going to restore the transaction log file which I had created at step 9 in the new database ExactHelp_Copy. In this case I will restore only those operation which had happened before my update operation time  2014/05/03 15:42:21:710 that is  lsn number 113000000016200001

RESTORE DATABASE ExactHelp_Copy
FROM  DISK = N'F:\DbBackup\ExactHelp_Log_1.bak'
WITH NORECOVERY ,
STOPBEFOREMARK =  'lsn:113000000016200001'

Step 13: Now I'm going to change the state of database ExactHelp_Copy from norecovery mode to recovery:

RESTORE DATABASE ExactHelp_Copy
WITH RECOVERY

Step 14: Now I'm going to check data in the table tblUser in new database ExactHelp_Copy:

USE ExactHelp_Copy

SELECT * FROM tblUser

ntUserID
vcUserName
vPassword
btActive
1
johnny_depp@exacthelp.com
sqlsecure
1
2
tom_hanks@exacthelp.com
sqlsecure
1
3
hahrukh_khan@exacthelp.com
sqlsecure
0
4
leonardo_di_caprio@exacthelp.com
sqlsecure
1
5
al_pacino@exacthelp.com
sqlsecure
1
6
jack_nicholson@exacthelp.com
secure01
1
7
kamal_haasan@exacthelp.com
secure01
0
8
tom_hiddleston@exacthelp.com
hiddleston
1
9
angelina_jolie@exacthelp.com
sqlsecure
1
10
charlize_theron@exacthelp.com
sqlsecure
1
11
marion_cotillard@exacthelp.com
sqlsecure
0
12
julia_roberts@exacthelp.com
sqlsecure
1

Yes, I got the old data!!!

Step 15: Updating the Exact_Help.tblUser from Exact_Help_Copy.tblUser by using key column ntUserID:

UPDATE tblUser SET
     vcUserName = CU.vcUserName,
     vPassword = CU.vPassword,
     btActive = CU.btActive
FROM  tblUser U INNER JOIN ExactHelp_Copy.dbo.tblUser CU
ON U.ntUserID = CU.ntUserID
WHERE U.vcUserName = 'kate_winslet@mfs.com'