Need help restoring database from older backup

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)

Need help restoring database from older backup

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2008-05-01 : 15:38:11

I am in the process of moving a database to a new location. My problem is that I do not have the most recent full backup file. I have a previous full backup file and I also have all of the transaction log backups from the time of the old backup to the present time.

My question is, do I need the most recent backup to restore the database, or can I do it with the older backup plus the transaction log backups?

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 15:39:40

If you have the entire transaction log since the older full backup, then you can use that to recover.

But can't you just backup the current database or is it unavailable?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2008-05-01 : 15:56:28

quote:
Originally posted by tkizer

If you have the entire transaction log since the older full backup, then you can use that to recover.

But can't you just backup the current database or is it unavailable?

Tara Kizer

I'm transferring a 100 gig database to a new server halfway across the country. It took 15 hours to copy Wednesday's backup to the new server. The database performed another full backup last night (Thursday 2 am), as well as a transaction log backup every two hours. If I can recover the database to the present time using only wednesday's backup plus the .trn files, that will be fine. Otherwise I'll have to have the database backed up to a USB drive and fedexed for saturday.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-01 : 16:08:12

"It took 15 hours to copy Wednesday's backup to the new server"

Didn't get you question clearly? So you mean you took full backup on wednesday and copied to destination server or what? Did you restore in norecovery mode so you can apply transaction log backup or diff backup.

Post your question clearly so we can answer.

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2008-05-01 : 16:24:41

Wednesday's full backup is copied to new server.

Thursday's full backup is not copied to new server.

All transaction log backups since Wednesday are/will be copied to new server.

Can I restore the database on the new server using Wednesday's full backup but not Thursday's full backup, plus all of the transaction log backups since Wednesday through tomorrow (Friday) morning?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 16:27:06

Like I said, yes.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-01 : 16:32:28

you have to use compressing tools like winzip,winrar,SQL LS while doing big file transfer. Or FTP task to move file instead of TCP/IP to gain speed rather than waiting 15 hours

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 16:35:26

He's not asking how to improve the speed of the file copy. It is obvious that one must compress files to get them to transfer faster.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-01 : 16:42:21

"It took 15 hours to copy Wednesday's backup to the new server"

Agreed.I am answering to this part.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 16:44:39

He isn't asking a question there. What he's saying is that he doesn't want to have to pull over Thursday's backup since it takes 15 hours to transfer a large file. And since he has the entire transaction log chain since Wednesday, he doesn't need Thursday's backup.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-01 : 16:46:07

I know . I am just adding to what you said .

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2008-05-02 : 08:50:08

Thanks Tara, that's what I needed to know.

sodeep, you're an idiot.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-02 : 08:57:27

quote:
Originally posted by stonebreaker
sodeep, you're an idiot.

Classic

--
Lumbago

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-02 : 14:19:06

You get these responses when you try to help this dumb and stupid (stonebreaker).Try to drink milk before you try to touch food. Try to walk first before trying to run.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-02 : 14:21:52

stonebreaker and sodeep,

Please be respectful to each other here. Failure to do so could mean we'll lock your accounts.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources