Restore gone bad - SQL Server Forums

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 Programming

Restore gone bad

Author

Topic

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 13:43:12

I re imaged my SQL server and needed to restore the current database backup from the previous night. I went through the normal restore process and got the message that the restore was complete...all was well... but today users of the database tell me their data is missing. Apparently the restore didn't take! I restored 2 database, and one took just fine, while the other didn't. The only difference I could think of is that I might have checked "Force restore over existing database" on one of the restores but not the other (maybe). Would having that checked or unchecked cause a database restore not to take???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 13:52:20

No. That option just means to overwrite the database if one exists with the same name already. If you reimaged your machine, you shouldn't have any databases there except for the system ones.

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

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 14:07:42

I restored 2 databases, one took one didn't. both gave me the "database restored successfully" message... I'm stumped, unless the backup routine is the one that failed?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 14:11:49

I don't know what is meant by "one took one didn't".

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

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 14:15:19

All info in one is current (Friday night's info). The info in the other is missing Wednesday, Thursday & Friday. Yet the backup files for both databases were the Friday night backups (verified by file date).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 14:21:05

The restore is fine then. You've got a problem with your backup.

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

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 14:43:37

tell me this... is it common for a SQL backup to take less than a minute, on a database that's 650MB's? (raid 5)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-17 : 14:49:11

yes.
just tried one of our 1 Gb db's on my dev box. took 20 seconds.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 14:58:21

so, I should realistically set my tlogs to backup, within a few minutes after the dbase? ex: 9:45pm for the dbase, and 9:50pm for the tlogs... correct?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 14:59:56

Not necessarily. What are you trying to achieve with transaction log backups? We run our transaction log backups every 15 minutes due to the criticality of the data.

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

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 15:03:47

hmmm... I was told that I needed to do so for this database and therefor have always scheduled them together. I'm not too familiar with exactly what they are and how they work, so I can't answer that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 15:10:18

Sounds like you need to talk to someone with more DBA knowledge then or start reading about backup/restore/point in time recovery/recovery models in SQL Server Books Online.

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

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 15:11:47

I agree...

T_A_C
Starting Member

8 Posts

Posted - 2007-12-17 : 15:19:07

One last question... when I restored (remember the server was reimaged with the Tuesday night image). In the ent manager, I ran the restore and it only showed me that Tuesday's backup was available. I changed the file location to the Friday night backup file and tlog, then ran the restore.

Was I missing a step in the process?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 15:24:15

Since the server was reimaged, you can not use the Enterprise Manager backup history information. That information is gone.

I'm not sure what step you missed.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources