Restoring a database - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

Restoring a database

Author

Topic

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-18 : 10:16:52

I hope this is the right place to ask this question. If not, please let me know.

I have been given a MS SQL Server 2008 backup database to restore and do some analysis on. Unfortunately, I nor anyone else here in the office has any MS SQL Server experience.

The backup file is called "xxxxx_backup_2010_02_11_210003_9644595.bak". I'm being told that this database was from a MS SQL Server 2008 Standard Edition and that I can load it up in MS SQL Server 2008 Express Edition. I've got the MS SQL Server 2008 Express Edition installed.

I've looked at the RESTORE command but am not sure what I need to do.

How can I restore this on my workstation?

Any help is appreciated.

Thanks

Scott Huerta

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2010-02-18 : 10:39:25

First, you need to find out the filenames included in the backup.

Run,
RESTORE FILELISTONLY FROM DISK = '[BackupLocation]'

This should list the database files. Generally on the server, the files will be on different disks which you may not have on your workstation so you need to move the files to a different location;

RESTORE DATABASE [DBName] FROM DISK = '[BackupLocation]'
WITH MOVE '[datafile]' TO 'C:\Folder\[datafile].mdf',
MOVE '[logfile]' TO 'C:\Folder\[logfile].ldf'

Hope this helps.

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 11:55:49

You may also have to sort out the Logical Names of the database.

Details of the Restore command, and how the various bits from RESTORE FILELSITONLY correspond to the parameters in the actual RESTORE command in my two posts here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#273265

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-18 : 13:32:53

Thank you for the replies. Here's my next newbie question: Where do I enter these commands? I tried in PowerShell, Command Prompt, and a query, but all gave me errors.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 13:38:08

You need Management Studio (for SQL Express) if you haven't already got it I think this is the link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=en

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-18 : 13:52:52

Thanks for the quick reply Kristen. Where in Management Studio do I run these commands?

I have it. Well this is what it says in the "About" option:

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3603
Operating System 5.1.2600

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 14:07:15

Haven't used Management Studio for Express, but on the Full Blown version you can RightClick the Server (or open up the Server & Database and RightClick a Database) in the Left Pane and then choose "New Query"

That will give you a window-pane that you can just type SQL commands into, then press Control-E to execute the SQL

Type

SELECT TOP 10 * FROM INFORMATION_SCHEMA.TABLES

if you want a benign query to try

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-18 : 14:11:36

Thank you. I just figured out where to do this. I must have had something wrong with the initial command that I had tried as the query had failed before I posted the question. Now that I know the correct place to run these commands I will try following the original posted suggestions. Thanks.

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-18 : 15:32:11

OK. I thought I was home free. But no such luck.

When running the restore command I'm getting the following messages in the Message window:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Msg 3203, Level 16, State 1, Line 3
Read on "c:\Temp\AlliedCP\FactorSoft_backup_2010_02_11_210003_9644595.bak" failed: 38(Reached the end of the file.)
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Did I do something incorrectly? Or is this indicative of a real data issue with the backup file? Is there a way to verify that this backup is in fact good or corrupted?

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2010-02-18 : 15:36:38

You can use RESTORE VERIFYONLY to verify the backup.

[url]http://technet.microsoft.com/en-us/library/ms188902.aspx[/url]

Was the backup operation striped across several files? Or backed up to just one file?

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-18 : 16:34:53

Thank you Dale.

The Verify command fails as well.

Since I'm doing this restore on my XP w/s and knowing that the backed up database file is 2.5GB in size, is this a problem? It looked like the resulting MDF & LDF files were going to be about 10GB combined.

Or is this a true case of a corrupted backup file?

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 01:31:09

Probably clutching at straws, but have you applied the latest service pack to your SQL Express setup?

Type SELECT @@Version and check the number here

http://www.sqlteam.com/article/sql-server-versions

DaleTurley
Yak Posting Veteran

76 Posts

Posted - 2010-02-19 : 04:14:40

It's possible that the backup could be that size, the backup will only back up the data that is stored in the file not the file size... For example a 10GB mdf file could only contain 100mb of data.

It does sound to me though that the backup was striped and there is a backup file missing...
Or similar to the route Kristen is going down, there could be something enabled in the live database that can't be used on SQL Server Express, Service Broker, full text search (only available with advanced services). Can you access the live database?

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 04:51:09

" The Verify command fails as well"

Did it give you a more useful error message than the RESTORE failure?

I wish MS put more effort into the error messages, or provided an online resource where the possible causes of MSG-IDs were fully discussed (maybe they do???)

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-19 : 09:52:48

Dale,

Here's the result of the SELECT @@Version command:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

It looks to me as though I do in fact need to install a patch to MS SQL Server 2008. Which update should I be putting on? The latest on the list in the table or the same level that was used with the database that we received?

Kristen, the message that I got from the RESTORE VERIFYONLY command generated the identical message that the RESTORE failed with.

Again, thank you for your help on this. I appreciate it.

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 10:25:31

Put on SP1

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

and if you like the latest cumulative update (currently http://support.microsoft.com/kb/977443/en-us)

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-19 : 13:14:15

Hi Kristen,

I've put on SP1 and then retried the RESTORE VERIFYONLY command and it still fails with the comment about reaching the end of the file. Do you see any benefit in updating to the latest cumulative update and retrying this again?

The customer that we got this file from is running without SP1 and on a 64-bit version. Is there any issue trying to retore a DB that was created on a 64-bit OS to a 32-bit OS?

Thanks,

Scott

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 13:20:41

"Do you see any benefit in updating to the latest cumulative update and retrying this again?"

I haven't read the Cumulative Update buglist, but assuming that doesn't specifically mention an issue which looks as thought it might be related then "no"

Request a fresh backup, check that they are NOT using multiple, striped, backup files [and if they are tell them they are twits and should have sent you the multiple files in the first place!], and ask them to do a Verify before sending it to you.

I doubt the backup is striped, and expect its just got knackered in transit. However, its odd that it got to 99% ... which suggested that it might be OK but some post-processing of the file was the problem (converting to your version or somesuch) - although I don't suppose RESTORE VERIFY bothers with that, I would expect it just checked the file validity, so on that basis the file is bust

)

"Is there any issue trying to retore a DB that was created on a 64-bit OS to a 32-bit OS?"

No (as in "I can't imagine why there would be" - SQL is suppose to not care about things like that - Famous-Last-Words!)

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-19 : 13:40:56

Thank you Kristen. I'll request a new back up along with your recommendations.

Thanks

ScottHuerta
Starting Member

11 Posts

Posted - 2010-02-25 : 15:09:55

I have received a new backup and I think it restored. The messages said it did. But when I see it in the MS SQL Server Management Studio in the treeview the folder icon has a green arrow pointing up and the database name has "(Restoring...)" after it. I've looked into the online help but haven't been able to determine what the "Restoring" literal means.

This was the command that I ran:

RESTORE DATABASE XXXXXXX
FROM DISK ='c:\Temp\AlliedCP\20100222'
WITH REPLACE, NORECOVERY, STATS=10,
MOVE 'FS_SMPL_dat' TO 'D:\MSSQL2008\ACP\MSSQL10.SCOTT\MSSQL\DATA\XXXXXXX.mdf',
MOVE 'FS_SMPL_log' TO 'D:\MSSQL2008\ACP\MSSQL10.SCOTT\MSSQL\DATA\XXXXXXX.ldf';

Is there something else I need to do at this stage?

Any and all help is appreciated.

Thanks,

Scott

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 18:08:28

I reckon you are OK:

NORECOVERY will leave the database "waiting" for more (e.g. transaction) backups to be restored. i.e. you can restore then FULL backup file, then each transaction log backup after that.

You need to tell it that you have finished recovering files:

RESTORE DATABASE MyDatabaseName WITH RECOVERY
GO

(i.e. you don't specify any restore files, just the "RECOVERY" command)

Best to run a DBCC CHECKDB once it says the DB is alive - just to check that there is no corruption.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources