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 2000 Forums
 SQL Server Administration (2000)
 how long will take to restore a 10 gb?

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 16:30:38
I tried to restore a complete back up into a stand alone box. the file is 10 gb from its c drive. the server is windows 2003 server. How long will it take? The Retore progress window did not show progress for 20+ minutes, shall I stop it?

X002548
Not Just a Number

15586 Posts
Posted - 2005-04-18 : 16:38:29
Takes me about 3 minutes for a 4GB db

What does Profiler show you...anything in the error logs?

Where's the code you used?



Brett

8-)
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 16:39:38
When I have a .bak file, is re-attach it to a sql instance faster than going through the restore?
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 16:44:19
One thing I forgot is to size the log and db before start the process, and also did not set it in simple mode. Those two steps will save me some time, I guess. But I did not use code, I just went in from menu, restore. It is frastrating to see the restore progress windows sitting there without showing any progress.
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 16:47:45
Odd. I would stop it and run it from Query Analyzer. 


RESTORE DATABASE MyDB FROM DISK = '<bakup file location>'
WITH NO RECOVERY, STATS

add the following if the disk locations are different

, MOVE 'MyDB' TO '<DB disk location>'


Are you trying to restore to the same volume? The STATS option will print progress in increments of 10%. You can also set this as desired, see your Books Online.
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 16:56:50
quote:
Originally posted by Hommer

When I have a .bak file, is re-attach it to a sql instance faster than going through the restore?


You can't attach a backup file.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 17:08:08
Jason, Thank you for the reply. So this whole thing can take hours long, and the restore time is production down time. What are the ways to minimize this time? I know ideally, hot standby is the key to any mission critical database, but that is not an option in my case.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-04-18 : 17:10:44
Having the fastest possible disk setup helps this process.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 17:27:12
Throughput is the key. The term is multi-faceted though. You need a basic understanding of system architectures and relational database design. As Michael pointed out, the disk sub-system has a lot to do with performance.

My guess is you are restoring to the same hardware? If SQL server is having to read from the backup file and write a new file of the same size to the same hardware, then the performance of your hard drive is your bottleneck. If at all possible, try restoring the file from a separate physical disk. If you have a high speed network (GB), you could even try restoring from the network, just use a UNC path vs the logical drive path.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2005-04-18 : 17:39:17
Are you doing restores a lot? Why do you have to restore the database? Was there some major error?

quote:
Originally posted by Hommer

...So this whole thing can take hours long, and the restore time is production down time. What are the ways to minimize this time?...


CODO ERGO SUM
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 17:52:47
We did not do a lot restore. I truncated a table, then have to get it back from backup.
Now I have the complete backup restored, but when I tried to restore the tlogs, it said I did not specify norevocery. I did not see that option when I got there from tools, restore,...(:
Any idea? Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-04-18 : 17:58:00
It's on the second tab (options) of the restore wizard. The second option in the recovery completion state section does this.

You can also do it with the RESTORE DATABASE command using the WITH NORECOVERY option.

Tara
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2005-04-18 : 18:11:11
If you did not specify NORECOVERY, you will have to do the restore again if you want to apply the transaction logs.

Also, if you had the disk space, you might have been better off restoring the database backup to a new database, and then copying over the data from the table you truncated. That way, you might have been able to leave your database online.

quote:
Originally posted by Hommer

We did not do a lot restore. I truncated a table, then have to get it back from backup.
Now I have the complete backup restored, but when I tried to restore the tlogs, it said I did not specify norevocery. I did not see that option when I got there from tools, restore,...(:
Any idea? Thanks!


CODO ERGO SUM
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 18:17:47
We have tlog backup files(20 of them) on a netwrok drive, and I copied them to sql server C. (Somehow I cannot map to it).
Under the restore wizard, does do I use database, filegroup/files, or device? I thought filegroup/files is the most reasonable one, but am getting the same error.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-04-18 : 18:20:32
Just do this from Query Analyzer as you are making this harder than it is.

RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\SomeBackupFile.BAK'
WITH NORECOVERY, REPLACE

Then for each subsequent tlog to be applied:

RESTORE LOG DBNameGoesHere
FROM DISK = 'C:\SomeTLogBackupFile1.BAK'
WITH NORECOVERY, REPLACE

RESTORE LOG DBNameGoesHere
FROM DISK = 'C:\SomeTLogBackupFile2.BAK'
WITH NORECOVERY, REPLACE

Then for the final tlog to be applied:

RESTORE LOG DBNameGoesHere
FROM DISK = 'C:\SomeTLogBackupFile3.BAK'
WITH RECOVERY, REPLACE




Tara
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 18:22:30
Try From Device, then Add, then click the button to browse the files.
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-18 : 18:26:33
quote:
Just do this from Query Analyzer as you are making this harder than it is.


You won't find a lot of patience for shyness with Query Analyzer (a.k.a, QA) in this forum...try appeasing the godess and insert that SQL into QA.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 19:19:51
How am I going to payback to you folks?
I told my co-workers that I am going to buy them a lunch.
I will try Tara's code in QA as soon as the complete backup is restore. During the process of restoring the tlog, the complete backup went away. What a day!
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 20:42:33
Now I got this Exclusive access could not be obtained because the db is in use when I tried Tara's resotre log command in QA...
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2005-04-18 : 21:56:37
disconnect the other users, one trick i learned is to take the db offline, then bring it online and run your query (remember not to use the database as current)

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

Hommer
Aged Yak Warrior

808 Posts
Posted - 2005-04-18 : 22:10:19

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site