Restoring Old backup over current data?

Question

Restoring Old backup over current data?

Rehan Ahmad Rattu

SSC Eights!

Points: 825
More actions
November 25, 2003 at 2:42 am

#107385

Need help in restoring Old backups over current (new) data in the database ... what kind of options are available in SQL to accomplish this. As native support for Archival is not available in SQL Server, backup and selective restoration is the only way out. Please help!!
- Rattu

Viewing 13 posts - 1 through 12 (of 12 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

In EM you can right-click on database -> All tasks -> Restore database...

Then you can choose a database, the backup you want to restore and on the options tab there is a checkbox 'Force Restore over existing database', which speaks for itself. At this point you (the sa) must be the only person connected to this database.

You can achieve the same with RESTORE DATABASE in T-SQL. You might want to take a look at BOL for explanation on this.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rehan Ahmad Rattu SSC Eights! Points: 825 More actions · Answer 2

Thanks Frank... you proved to be a Guru for me ... solved the problem at once. (k)ool ...

but tell me what is the reason for a single User Connected to Database ... and what is the remedy for it ... if other Users can be allowed to continue using the database ...???

🙂

quote:
In EM you can right-click on database -> All tasks -> Restore database...
Then you can choose a database, the backup you want to restore and on the options tab there is a checkbox 'Force Restore over existing database', which speaks for itself. At this point you (the sa) must be the only person connected to this database.
You can achieve the same with RESTORE DATABASE in T-SQL. You might want to take a look at BOL for explanation on this.
Frank
http://www.insidesql.de
http://www.familienzirkus.de

- Rattu

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Thanks for your kind words.

I am not a guru by any means! I use this forum to learn SQL Server (might be selfish).

Never tried what happens when there is another user, but I guess the process won't start at all or will fail.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

PurpleLady SSCertifiable Points: 7819 More actions · Answer 4

I do restores on a daily basis. If another user is connected to the database - whether or not they are actively using it, the restore will fail. There's no way around it. Nor would you want to allow access to the data until the process was complete. The user could get some very skewed results. In my processes, I kill all connections prior to performing the restore.

Rehan Ahmad Rattu SSC Eights! Points: 825 More actions · Answer 5

Hi there. Kalis please tell me full procedure for making a backup and restoring it over current database without lossing current data.

reply quick

😉

- Rattu

Rehan Ahmad Rattu SSC Eights! Points: 825 More actions · Answer 6

hey here is the situation ... please advise how to do this kind of backups...

initially table(s) data

RowId - RowData

1 - row number 1

2 - row number 2

3 - row number 3

here backup is taken ... now

a new row is entered...

INSERTED: 4 - row number 4

and a row is deleted from the table...

DELETED: 2 - row number 2

table is now

1 - row number 1

3 - row number 3

4 - row number 4

i need to take backup here ... that will restore old values that aren't in the database now ... but still i want all those new values that were updated since the backup is taken...

after restore the table state should be

1 - row number 1

2 - row number 2

3 - row number 3

4 - row number 4

................... HELP

Edited by - rattu on 11/30/2003 10:51:36 PM

- Rattu

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

The transactions since your last backup should be kept in your log file.

I'm not really sure if I understand you, but I would take a look at Books Online for RESTORE LOG

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rehan Ahmad Rattu SSC Eights! Points: 825 More actions · Answer 8

i think that transaction log is not required ... but i want to restore data from backup without over-writing existing data in the database ... without overwriting the current data...

Frank i am counting on you and would like to have a reply in some quick time ...

Rehan

- Rattu

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

You are not putting any pressure on me, are you?

I still think that doing a restore of your log file should yield the desired result or am I missing something?

Given your example your final result is the restoration of the deleted row 2. This transaction *should* be in the log file. Now, assuming no other data modification has taken place, this is the only action to do for you.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rehan Ahmad Rattu SSC Eights! Points: 825 More actions · Answer 10

Hi Frank,

I am not into taking transactional backups yet. Can you please teach me how to accomplish this kind of backups and restoration ... i will be thankful to you

take care

waiting for your reply ...

quote:
You are not putting any pressure on me, are you?
I still think that doing a restore of your log file should yield the desired result or am I missing something?
Given your example your final result is the restoration of the deleted row 2. This transaction *should* be in the log file. Now, assuming no other data modification has taken place, this is the only action to do for you.
Frank
http://www.insidesql.de
http://www.familienzirkus.de

- Rattu

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

Hit the button too early...

Sorry, for replying now. I lost track of this thread and missed your last reply.

Let me start by describing what I do here.

- Each night a full backup is taken

- Logs are backed up each hour between 8:00 and 21:00

- The 'important' databases are set to 'Full Recovery' model

- Backup and Log for 2 days are kept on disk

- Once a week an image is burned on DVD and safely kept outside the server room.

Your question cannot be answered in an online forum I guess. This should be part of your disaster recovery plan, which IMHO should be based on a management decisions.

There are several entry points in BOL.

Look at

BACKUP

RESTORE

recovery models

troubleshooting SQL Server -> backing up and restoring

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]