February 14, 2008 at 2:12 am
Hi I have setup a new server and am having problems recovering data,
Yesterday i asked a user to change some details between 11:00-15:00hrs,
my set up
recovery model : full
at 19:00hrs I take a full database backup
BACKUP DATABASE [Changepoint] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Changepoint_db_200712121434.BAK' WITH INIT
at 11:00hrs I take a transacation log backup which overwrites the previous backups of the transaction log
Backup LOG [Changepoint] TO DISK = N'd:\temp\CPTRNLOG.BAK' WITH INIT
at 15:00hrs I take a second transaction log backup which appends to the current backup file for 11:00
Backup LOG [Changepoint] TO DISK = N'd:\temp\CPTRNLOG.BAK'
I asked the user to make a change to some data at 14:00hrs
and then waited till 16:00 after the 15:00hrs transaction log backup was taken.
I then run this command to restore the data to 15:00
ALTER DATABASE Changepoint SET OFFLINE
RESTORE Database [Changepoint] From Disk = N'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Changepoint_db_200712121434.BAK'WITH NORECOVERY
RESTORE LOG [Changepoint] FROM DISK = N'd:\temp\CPTRNLOG.bak'
ALTER DATABASE Changepoint SET ONLINE
I then asked the user to check to see if the data was there and they told me it was not ?
I do not understand why this has happened, can someone check my commands above and let me know what i am doing wrong please, I am not an expert in SQL quite new to this so can someone please help me!!
Thanks you for your Time,
Frank:)
February 14, 2008 at 2:49 am
Franco,
sounds like you have only restored your first transaction log - ie your database is back to it's 11am state.
You need to change your restore log statement, to restore both files... use the WITH FILE = clause to do this...
ie
RESTORE LOG [Changepoint] FROM DISK = N'd:\temp\CPTRNLOG.bak' WITH FILE=1, NORECOVERY
then, restore your second backup taken at 3pm.
RESTORE LOG [Changepoint] FROM DISK = N'd:\temp\CPTRNLOG.bak' WITH FILE=2,RECOVERY
Probably better in future to backup logs to different filenames, and include a timestamp as part of the filename eg d:\temp\CPTRNLOG_1100.bak' , d:\temp\CPTRNLOG_1500.bak' to avoid this....
February 14, 2008 at 2:59 am
I was thinking it could be something like that so just trying to restore only with the 11:00 to see if that was the problem,
Thanks for confirming the problem and the solution two seperate file !! cool thanks did not think I could do this, but obviously can so will do this to save confusion in future.
Thanks Will test it out and see what happens.
Cheers
Frank;)
February 14, 2008 at 3:25 am
When in doubt, use the GUI tools to investigate what it thinks exists in backups, then use its scripting feature to script out your backups and restores. This way you can see an overview of what's happening and be sure to not leave anything out.
Separate files are definitely the way to go!
February 14, 2008 at 3:31 am
Hi ,
Are you talking about Enterprise manager I am using sql 2000 ent edition.
what gui tools are you talking about if not Ent manager,
and what tools does all your scripting ?
Cheers
Frank.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply