SQL Server Performance Forum – Threads Archive
Restoring a database to a new computer
Ok, first a disclaimer. I am not an SQL DBA by any means. So I am completly unfamiliar with a lot of the capabilities, and options. With that being said, I’m the one that got stuck with this, so I have to figure out a way of making it work. The Problem:We we given a .bak file from an SQL database that contains data that we need for our analysis. When we brought the file back here, we attempted to restore it. I can restore the file without too much trouble, but it doesn’t seem to contain all the data that we’re sure should be there. For example, a table that should have several thousand rows, only has 44. The only error that comes up when we’re restoring the file is:
"bypassing recovery for database ‘test’ because it is marked IN LOAD" I’m not sure why that would be causing a problem. And I’m not sure what’s going on. I could understand problems that would cause a table not to be loaded, or none of the data to be loaded into that table, but I’m unsure what would make it only load some data into a table. The original file size that we’re restoring from is like 600MB. After we’ve restored the file, if we turn around and back it up again, the file size of the new backup is only like 100MB. So something isn’t going right here. If I could figure out how to find the damn version of SQL we’re using, I’d tell you, but all I can find is the version of the Enterprise Manager. Thank you for any help you can give, and please remember that I’m an SQL idiot, so use small words, and lots of them.
Just use RESTORE DATABASE dbname WITH RECOVERY which enables database in USE and then you can check the rows and data. To findout the version of SQL using from Query analyzer issue SELECT @@VERSION.
As a first hand help refer to SQL server books online which will be installed alongwith SQL server.
Any issue feel free to come back. Good luck. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks satya. I’m sorry to sound so dense, but I’m an oracle person. Is there some type of command line I should be typing this into? Does SQL server have something like SQL Plus that Oracle has? Do I need to create a database first? This isn’t a database that we have, it’s like brand new to us, so will that statement create a new database with a name that I specify? That’s what it looks like, but I just wanted to be sure. And I did pursue the help files, but while they told me how to restore the database, it couldn’t address the reason we were getting that error.
I should be specific while referring the statement, you can use Query Analyzer which will be installed alongwith SQL instance. Start –> Programs –> SQL Server program group –> Query Analyzer or from Enterprise Manager –> Tools –> SQL Query Analyzer. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
*rubs head*…I haven’t felt this stupid in a long time. Ok, problems….When I use that statement, if I’m reading it right, it’s going to restore a database that already exists. But I don’t have anything except a .bak file. I’m not even sure what they named they’re original database. Do I need to specify what .bak file to use? Should I be placing that .bak file in a certain directory, so that the SQL server sees it automatically? I’m using SQL Server 2000 if that helps. I can get the command to run, but it’s not pointing to the correct file, so it’s not really restoring any files. I’m really sorry that I’m taking so much time and effort.
No bother, use the following statement to restore the database using that .bak file
Make sure to copy that .bak file to the SQL server drive. RESTORE DATABASE <dbname> from disk=’\pathfilename.bak’ WITH RECOVERY
whereas dbname is the database name
\pathfilename.bak is the complete path where the file is copied. As referred earlier take time to read SQL server books online. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>