Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 how to roll back an update

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 16:11:09
hey guys, so i've set up all my backups and all that fun stuff, and now i have an issue where i accidentially updated the wrong records, and i need to restore from a t-log...

the database does the following backups:
-Weekly FULL
-12hr Diffs (12:00am/12:00pm)
-15min T-logs (@ very quarter hour)

i error happened @ 1:11pm, what steps do i have to go though, to roll back that one transaction? (p.s. it's already been committed)

thanks!

X002548
Not Just a Number

15586 Posts
Posted - 2010-08-02 : 16:17:05
It's not a rollback, its a RESTORE

You'll need to Restore the 12:00PM dump, and then apply the logs in order



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-08-02 : 16:19:16
Restore weekly full
Restore diff at 12pm
Restore tlogs: 12:15pm, 12:30pm, 12:45pm, 1pm, 1:15pm (use STOPAT for last one to do point in time rerecovery)

Specify WITH NORECOVERY for each until you've found the correct STOPAT, then use WITH RECOVERY on it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-08-02 : 16:19:49
I forgot to mention to do that to a separate database and then once you've got your data how you want it, move the data to the production database via T-SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 16:40:08
thanks...

any programatic way to restore each t-log? or just lots of f5ing? :D
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-08-02 : 16:51:56
I have a helper script, but it's for SQL Litespeed.

You should only need to run 6 restore commands, so it shouldn't be too hard to write them out and then F5.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 17:21:02
THANKS!
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 17:35:20
okay, how do i deal with this one?

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'TESTDB_Restore' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 17:35:43
tried: 

RESTORE DATABASE [TESTDB_Restore] FROM  DISK = N'I:\MSSQL\BACKUP\SQL3\TestDB\TestDB_20100731233020.BAK' 
WITH  
FILE = 1,  
	MOVE N'TestDB_FileGroup1' TO N'C:\Drives\FileGroup1\SQLDatabases\TESTDB_Restore_FileGroup1.mdf',  
	MOVE N'TestDB_FileGroup2' TO N'C:\Drives\FileGroup1\SQLDatabases\TESTDB_Restore_FileGroup2.ndf',  
	MOVE N'TestDB_FileGroup3' TO N'C:\Drives\FileGroup2\SQLDatabases\TESTDB_Restore_FileGroup3.ndf',  
	MOVE N'TestDB_FileGroup4' TO N'C:\Drives\FileGroup2\SQLDatabases\TESTDB_Restore_FileGroup4.ndf',  
	MOVE N'TestDB_FileGroup5' TO N'C:\Drives\FileGroup3\SQLDatabases\TESTDB_Restore_FileGroup5.ndf',  
	MOVE N'TestDB_FileGroup6' TO N'C:\Drives\FileGroup3\SQLDatabases\TESTDB_Restore_FileGroup6.ndf',  
	MOVE N'TestDB_FileGroup7' TO N'C:\Drives\FileGroup4\SQLDatabases\TESTDB_Restore_FileGroup7.ndf',  
	MOVE N'TestDB_FileGroup8' TO N'C:\Drives\FileGroup4\SQLDatabases\TESTDB_Restore_FileGroup8.ndf',  
	MOVE N'TestDB_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TESTDB_Restore_log.ldf',  
NOUNLOAD,  STATS = 10
GO


original db name was TESTDB
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-08-02 : 17:38:38
You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 18:15:27
uh.. i think our messages crossed :D /poke...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-08-02 : 18:17:22
Yes sorry, had the page open too long when I posted. Should've done a refresh before I replied.

Run RESTORE FILELISTONLY and RESTORE HEADERONLY to ensure everything is right.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 18:34:04
okay so i can see everything, but as you said, i wanna restore this to a different DB, so all the backup files are for TESTDB i wanna restore them to a new DB named: TestDB_Restore but every time i try, it says i'm trying to restore to a different DB name.. that's what im trying to get around...
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 18:36:44
btw, the original TestDB is on our Production server, i'm trying to restore to our DEV server, two different machines...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-08-02 : 18:40:00
Could you post the output of RESTORE FILELISTONLY?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 18:59:52
[code]LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint
TestDB_FileGroup1 C:\Drives\FileGroup1\SQLDatabases\TestDB_FileGroup1.mdf D PRIMARY 290538258432 35184372080640 1 0 0 E9961469-7F07-4516-9BE6-3EABECBAB884 0 0 113332387840 512 1 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_FileGroup2 C:\Drives\FileGroup2\SQLDatabases\TestDB_FileGroup2.ndf D FileGroup2 197296324608 35184372080640 3 19000000006000002 0 9129CD42-F716-45DF-8AB7-B08F40088439 0 0 99142991872 512 2 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_FileGroup3 C:\Drives\FileGroup3\SQLDatabases\TestDB_FileGroup3.ndf D FileGroup3 177885282304 35184372080640 4 19000000008700002 0 3E8C67D5-7DE6-49D6-BCB8-662EDB2C3D99 0 0 97377779712 512 3 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_FileGroup4 C:\Drives\FileGroup4\SQLDatabases\TestDB_FileGroup4.ndf D FileGroup4 172866994176 35184372080640 5 19000000011400002 0 02A688BE-05AA-45B6-AB44-67E952EA9CA1 0 0 97138311168 512 4 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_FileGroup5 C:\Drives\FileGroup5\SQLDatabases\TestDB_FileGroup5.ndf D FileGroup5 167332151296 35184372080640 6 19000000014100002 0 2889A311-6AD7-49C1-B42B-CF911CCD9D3D 0 0 97493647360 512 5 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_FileGroup6 C:\Drives\FileGroup6\SQLDatabases\TestDB_FileGroup6.ndf D FileGroup6 173357465600 35184372080640 7 19000000016800002 0 ED3773AD-B678-4924-9708-68132BA2B31A 0 0 96374030336 512 6 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_FileGroup7 C:\Drives\FileGroup7\SQLDatabases\TestDB_FileGroup7.ndf D FileGroup7 104163246080 35184372080640 8 19000000019500002 0 9856795F-D166-46C6-9D06-738AE3A83692 0 0 96600850432 512 7 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_FileGroup8 C:\Drives\FileGroup8\SQLDatabases\TestDB_FileGroup8.ndf D FileGroup8 182450782208 35184372080640 9 19000000022200002 0 F67EA86E-DE7B-4C62-A972-D9C6C1EC28CF 0 0 151259643904 512 8 NULL 868973000005022200040 D5CB5CD1-A560-4773-8E3A-5273E21F951D 0 1 NULL
TestDB_log E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf L NULL 5740036096 2199023255552 2 0 0 3C8F5B2B-37A9-4247-9D54-218867AE91DC 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL
[/code]
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-02 : 19:14:18
sorry so wide :(
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2010-08-03 : 00:04:08
ran it with the REPLACE switch, seems to be doing the job (moved files to the testdb_restore file names)

thanks for the help though!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site