| Author |
 Topic  |
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
 Posted - 04/18/2005 : 22:49:54
|
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... |
 |
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 04/18/2005 : 23:13:37
|
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. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/19/2005 : 08:32:53
|
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 |
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 04/19/2005 : 09:41:29
|
| 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. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 04/19/2005 : 10:10:21
|
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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/20/2005 : 07:36:22
|
... 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 |
|
|
|
mr_mist
Grunnio
United Kingdom
1870 Posts |
Posted - 04/20/2005 : 07:51:19
|
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. :) |
|
|
|
Topic |
|
how long will take to restore a 10 gb?
