SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 how long will take to restore a 10 gb?
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/18/2005 :  22:49:54  Show Profile  Send jen a Yahoo! Message  Reply with Quote
in qa, you have to use another database (drop down list or issue a "use master" before running the script) aside from the one you're restoring so that the process can have exclusive access



--------------------
keeping it simple...
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 04/18/2005 :  23:13:37  Show Profile  Reply with Quote
We got Microsoft support on the line. Now I started 4th time of restoring the complete backup.

Last time, the options of the backup wizard is on "leave db operational, no additional tlog allowed." I guess this is equivelent of running Tara's first block of code, but with "WITH RECOVERY" instead of "WITHNORECOVERY".

MS said that is why the tlog would not restore.
Now we changed it to "leave db NONoperational, but will allow additional tlog backup. And re-run the restore of the bak. Hopefully, it will solve the problem.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/19/2005 :  08:32:53  Show Profile  Reply with Quote
In my experience the size of the original database MDF file, and its LDF log file, rather than the size of the backup file, is very influential in the recovery time. So if you ahve lots of unused space in your database (because you did some massive cleardown at some time, and haven't yet reused all that space (or shrunk the database), and similarly on your Log file, then all that "available space" has to be pre-created, and pre-initialised before teh actual restore can start.

When restoring in QA I also add

STATS = 10

(in the WITH clause) to show me progress during the restore - but on a big database the pre-create/initialise can take ages before the first percentage figure appears.

Kristen
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 04/19/2005 :  09:41:29  Show Profile  Reply with Quote
And after the stats reached 100% (took 20 minutes for the 8 gb tlog, (it is large because it is the first one created on Monday with all weekend's activities, but is it normal to have a 8 gb tlog for a 9 gb db?)), it took another 8 minutes to complete.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/19/2005 :  10:10:21  Show Profile  Reply with Quote
It sounds like you are not running trasaction log backups often enough. I usually set them up to run every 15 minutes all day, every day of the week. I also usually setup at least a daily full backup, so that I don't have to recover from several days back.

quote:
Originally posted by Hommer

And after the stats reached 100% (took 20 minutes for the 8 gb tlog, (it is large because it is the first one created on Monday with all weekend's activities, but is it normal to have a 8 gb tlog for a 9 gb db?)), it took another 8 minutes to complete.



CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/20/2005 :  07:36:22  Show Profile  Reply with Quote
... or a weekly [say] full backup and daily Differentials, and then frequent Transaction backups.

You've probably got some houskeeping (index rebuilds etc.) scheduled for over-weekend, which will create some big transaction logs.

Probably better to take a Transaction backup (so it empties it), then a Full Backup, and then restore that - no transaction logs required then. If there is shedloads of unused space in Database or Transaction files I would do a shrink first (but don't do that if the space is just enough for normal weekly ebb-and-flow - its costs plenty for SQL Server to re-obtain the space from the operating system,a nd leads to greater disk fragmentation).

Kristen
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 04/20/2005 :  07:51:19  Show Profile  Visit mr_mist's Homepage  Reply with Quote
quote:
Originally posted by Hommer


What did you mean by "not to use the database as current"?
I'd appriciated you give me a little more details.




First, if you're doing the restore from QA, then you need to make sure you are disconnected from teh database in Enterprise Manager, and that everyone else is, too.

Second, change the drop-down box to master.

-------
Moo. :)
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000