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.
| Author |
Topic |
|
fruddie
Starting Member
3 Posts |
 Posted - 2010-12-07 : 17:51:12
|
| Hi,I'm trying to restore a 50Gb full backup .bak file onto my local machine yet it seems awful slow. Eg I ran for an hour yesterday using transact SQL's "restore" command and it only processed 6 percent.I'm a .NET developer and I normally have no trouble with restoring from a .bak file for small db's. eg a few hundred Mb.I've tried doing this directly via SQL and through enterprise manager.Can anyone give me some tips?The transact SQL script I run which is taking forever is:RESTORE DATABASE [DbName] FROM DISK = N'C:\Db.BAK' WITH FILE = 1, MOVE N'DbName_dat' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DbName_data.mdf', MOVE N'DbName_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DbName_log.ldf', NOUNLOAD, REPLACE, STATS = 10GOPerhaps I should be going about this differently due to the size of the .bak file?Thanks for any help or suggestionsAndrew |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fruddie
Starting Member
3 Posts |
Posted - 2010-12-07 : 19:49:32
|
| Correct I only have a C drive on my dev machine. I'm trying to restore the db to this machine.Thanks for any help or suggestionsAndrew |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-08 : 04:05:50
|
| I don't think a 50GB database restore should take long - even on a mickey mouse machine - so I suspect that something is wrong.Perhaps SQL is set to use all available memory, but it sounds like this is not a dedicated SQL Server machine, so perhaps SQL is arguing with the other applications over memory and everything getting swapped out to the paging file?You could try explicitly setting how much (or rather how little!) memory SQL is allowed to have and see if that helps. Perhaps let it have 50% of the total memory (but leave at least 1GB RAM for the O/S, and preferably 1GB per CPU, and then however much everything else on your machine needs.Close all the applications you aren't using and stop any unnecessary services to free up more memory.SQL will pre-create the whole database file before it starts restoring - so there might be quite a delay before the restore actually starts showing any %-done figuresSQL will pre-create the database to the size of the original database - which may be MUCH larger than the size of the BAK file. |
|
|
|
fruddie
Starting Member
3 Posts |
Posted - 2010-12-14 : 00:24:57
|
| Thanks Kristen, You're on the money in answering my question.I'm wondering how to: "You could try explicitly setting how much (or rather how little!) memory SQL is allowed to have"Is that "right click --> set priority" for the SQL process in process explorer?Thanks for any help or suggestionsAndrew |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Restoring .bak file --> Tips for a .NET Developer