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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Hommer
Aged Yak Warrior

784 Posts

Posted - 04/18/2005 :  16:30:38  Show Profile  Reply with Quote
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 - 04/18/2005 :  16:38:29  Show Profile  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  16:39:38  Show Profile  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  16:44:19  Show Profile  Reply with Quote
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 - 04/18/2005 :  16:47:45  Show Profile  Reply with Quote
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.

Edited by - jason on 04/18/2005 16:51:12
Go to Top of Page

jason
Posting Yak Master

164 Posts

Posted - 04/18/2005 :  16:56:50  Show Profile  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  17:08:08  Show Profile  Reply with Quote
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

USA
2489 Posts

Posted - 04/18/2005 :  17:10:44  Show Profile  Visit MichaelP's Homepage  Reply with Quote
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 - 04/18/2005 :  17:27:12  Show Profile  Reply with Quote
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.

Edited by - jason on 04/18/2005 17:28:25
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/18/2005 :  17:39:17  Show Profile  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  17:52:47  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 04/18/2005 :  17:58:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 04/18/2005 :  18:11:11  Show Profile  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  18:17:47  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 04/18/2005 :  18:20:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/18/2005 :  18:22:30  Show Profile  Reply with Quote
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 - 04/18/2005 :  18:26:33  Show Profile  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  19:19:51  Show Profile  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  20:42:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/18/2005 :  21:56:37  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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

784 Posts

Posted - 04/18/2005 :  22:10:19  Show Profile  Reply with Quote

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
Page: of 2 Previous Topic Topic Next Topic  
Next 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.09 seconds. Powered By: Snitz Forums 2000