Lost data when restoring database from file

Question

Lost data when restoring database from file

LowFatSpread

Mr or Mrs. 500

Points: 574
More actions
February 24, 2012 at 9:02 pm

#256426

Hi There,
I have a database which stores settings from a cms. The settings change very frequently so I need to make sure that I capture the whole dataset when I back up. I've tried the script below but It's not recovering recent changes made to the database. I wanting to be able to backup the database file and then if needed restore it back to it's original state.
What am I missing with this procedure?
use master
GO
ALTER DATABASE SIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE SIS FROM DISK='c:\mypath\myfile.bak' with replace
GO

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

M&M SSC-Insane Points: 21700 More actions · Answer 1

I've tried the script below but It's not recovering recent changes made to the database.
RESTORE DATABASE SIS FROM DISK='c:\mypath\myfile.bak' with replace
GO

For restoring recent changes are you having transaction log backups? It looks like you are having only the latest full backup.

Also, what is the recovery model of this database and do you have transaction log backups at all?

M&M

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Research Point-in-Time recovery which includes having the target database in Full Backup Model, taking both Full and Transaction Log backups.

Also check tail-log backup.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

LowFatSpread Mr or Mrs. 500 Points: 574 More actions · Answer 3

Hi There,

For the recovery model. Given that i'm no sql server guru. Recovery

page verify - checksum.

How can I back up everything logs, database from a procedure? Do you have any code snippets for this?

sengeet86 Ten Centuries Points: 1252 More actions · Answer 4

LowFatSpread (2/25/2012)
Hi There,
For the recovery model. Given that i'm no sql server guru. Recovery
page verify - checksum.
How can I back up everything logs, database from a procedure? Do you have any code snippets for this?

Impact of the Recovery Model on Backup and Restore :-

http://msdn.microsoft.com/en-us/library/ms191239.aspx

java programming

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

LowFatSpread (2/25/2012)
Hi There,
For the recovery model. Given that i'm no sql server guru. Recovery
page verify - checksum.
How can I back up everything logs, database from a procedure? Do you have any code snippets for this?

if you run this

select name, recovery_model_desc from sys.databases

What is the desc for the database in question?

Depending on what the desc is, depends on what you can restore and if you need to change your backup routines.

LowFatSpread Mr or Mrs. 500 Points: 574 More actions · Answer 6

Hi There,

when I run

select name, recovery_model_desc from sys.databases

I get

SISFULL

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

Ok so you can do PIT restores.

Ok what is your backup routine? Full daily/weekly/monthly? Diffs daily/weekly/monthly TXLogs 5mins/15mins/Hourly/Daily etc

If you dont do TXLog backups then you cant do a PIT restore if you do, you will want to do something like this

RESTORE DATABASE SIS FROM DISK = 'Path To Bak File' WITH NORECOVERY

GO

RESTORE LOG SIS FROM DISK = 'Path To 1st TX Log Since Full Bak File' WITH NORECOVERY

GO

RESTORE LOG SIS FROM DISK = 'Path To 2nd TX Log Since Full Bak File' WITH NORECOVERY

GO

repeat the above until the last TXLog

RESTORE LOG SIS FROM DISK = 'Path To Last TX Log Since Full Bak File' WITH RECOVERY

GO

LowFatSpread Mr or Mrs. 500 Points: 574 More actions · Answer 8

ok. thanks very much for the answer.

i'll give it a try and let you know how it all goes.

sean

LowFatSpread Mr or Mrs. 500 Points: 574 More actions · Answer 9

Hi There,

So I scripted the backup and it seems to work. I tried to use the restore as recommended but I'm having some issues.

What am I missing here?

USE SIS;

GO

BACKUP DATABASE SIS

TO DISK='c:\mypath\myfile-backup.bak'

WITH FORMAT,

MEDIANAME = 'xx_backups',

NAME = 'Full Backup';

GO

USE SIS;

GO

BACKUP LOG SIS TO SIS_log WITH INIT

GO

!---------------------

use master

GO

ALTER DATABASE SIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE SIS FROM DISK = 'c:\mypath\myfile-backup.bak' WITH NORECOVERY

GO

RESTORE LOG SIS FROM DISK = 'c:\mypath\myfile-backup.bak' WITH NORECOVERY

GO

ALTER DATABASE SIS SET MULTI_USER WITH NO_WAIT

go

----

Msg 3159, Level 16, State 1, Line 2

The tail of the log for the database "SIS" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.