Restoring a Table - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Restoring a Table

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-01-21 : 12:02:40

Ok I'm just confirming I'm doing this correctly. Lets say someone accidentally drops a table on 01/18/2008 and I need to restore it. I have full backup that was done on the 17th and a differential done on the 16th. Would I restore from the differential done on the 17th??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-21 : 12:21:06

Then just restore from Fullbackup of 17th with diff name and Export that table to your original database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 12:27:23

You can't use the differential from the 16th. Use the full backup on the 17th and then apply whatever diff and tlog backups you have that gets you to 01/18/2008.

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

Subscribe to my blog

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-01-21 : 23:04:59

Ok guys thank you

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-01-26 : 11:09:21

The current backups are Differential and Full, the databases are in Simple recovery

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 11:31:10

Then you definitely can't restore to a point in time. You can only get as far as the last full or diff before the table was dropped, which means you would lose data.

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

Subscribe to my blog

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-01-26 : 13:04:13

ok thats what I was afraid of, thank you. this has no happened I'm just testing. The database have been changed to Full Recovery (The production Databases that is)

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 13:09:36

Make sure you backup your transaction logs on a regular basis now that you've switched to full recovery model. We backup ours every 15 minutes. If you don't do this, then you'll blow out the log eventually.

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

Subscribe to my blog

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-01-26 : 13:16:46

the system databases like the master are in full recovery mode they are being back up full and diff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 13:23:03

I'm referring to your user databases. Full and diff backups do not backup the transaction, so you aren't covered with point in time recovery until you start backing up the transaction log on a regular basis.

The system databases should not be in full recovery model. Why did you change this?

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

Subscribe to my blog

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-01-26 : 13:51:37

The transaction logs are backed up the production database are set at Full Recovery. Was seeking advice on system dbs, I will check out THE BOL

also considering a safety trigger

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [safety] 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK ;

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [safety] ON DATABASE

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 13:58:12

I handle your "safety" trigger by not providing those permissions in the production environment.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources