Database Recovery - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Recovery

Author

Topic

avalekya
Starting Member

7 Posts

Posted - 2006-03-07 : 01:31:01

Hi ,

Can someone tell me if it is possible to Recover a Database
from a Sql Server other than the Server from which the original backup was taken..??

Thanks,
Avalekya.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 01:34:24

yes, check backup and restore in BOL

or you can post more details for more information

--------------------
keeping it simple...

avalekya
Starting Member

7 Posts

Posted - 2006-03-07 : 02:12:47

Hi,

I did go through the RESTORE DATABASE WITH MOVE
TSQL statement .. but i get a error

Cannot open backup device 'c:\eng1.bak'. Device error or device off-line. See the SQL Server error log for more details.

Note: The backup was taken from a server in another
machine,then the backup file was copied to my machine,im tryin
to restore it in my sql server.(this is not a client of the first server)

Thanks,
Avalekya

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 02:20:18

no problem...

place the backup file in the server's drive, not yours then try again

the restore checks locally if the path is not unc

--------------------
keeping it simple...

avalekya
Starting Member

7 Posts

Posted - 2006-03-07 : 02:28:10

Hi,

I think im mot coming across clearly.
I'll describe again.

Me and my friend each have sql server installed on our machines. Each has server installation.
He wants to check in case his sql server has a problem can he restore the backup on my sql server.
Both are different SQL servers.
My backup file is already in C drive where my SQL Server is installed.

Thanks,
Avalekya.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 02:29:43

is the file copy done? if yes then
can you post your restore tsql...

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 02:35:54

yes you can... and do not edit your post...

so we can follow through

simplifying your question:

server A: backup1
server B: copy of backup1

question: will you be able to restore in server B? YES

--edit, i just realized, do you have the same version?

--------------------
keeping it simple...

avalekya
Starting Member

7 Posts

Posted - 2006-03-07 : 02:43:26

Yes both of us have installed SQL Server 2000.

Thanks,
avalekya

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 02:46:02

then post your tsql and let's take a look...

also, most often, when backup is created, the extension tends to be dropped off...

--------------------
keeping it simple...

avalekya
Starting Member

7 Posts

Posted - 2006-03-07 : 02:49:57

RESTORE DATABASE results
FROM DISK = 'C:\Program Files\Microsoft SQL Server\eng1.bak'
WITH MOVE 'eng1' TO 'c:\test\results.mdf',
MOVE 'eng1_log' TO 'c:\test\results.ldf'

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 03:31:21

the error you provided doesn't confirm your restore statement

verify if this is existing
'C:\Program Files\Microsoft SQL Server\eng1.bak'

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-03-07 : 03:55:21

Hi avalekya, Welcome to SQL Team!

See also

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE+syntax

for a worked example of Restore Syntax

Kristen

avalekya
Starting Member

7 Posts

Posted - 2006-03-07 : 04:26:15

Thank you jen but the backup file does exists in that location.

Kristen
Test

22859 Posts

Posted - 2006-03-07 : 04:45:06

"RESTORE DATABASE results
FROM DISK = 'C:\Program Files\Microsoft SQL Server\eng1.bak'
WITH MOVE 'eng1' TO 'c:\test\results.mdf',
MOVE 'eng1_log' TO 'c:\test\results.ldf'"

I reckon you may have problems restoring from a folder with a space in its name.

Try moving eng1.bak to C:\ and trying:

RESTORE DATABASE results
FROM DISK = 'C:\eng1.bak'
WITH MOVE 'eng1' TO 'c:\test\results.mdf',
MOVE 'eng1_log' TO 'c:\test\results.ldf'

If that doesn't work please post the error message you get (verbatim) and pls also post the results you get from

RESTORE FILELISTONLY FROM DISK = 'C:\eng1.bak'

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 05:07:54

use dos filenames

program files = progra~1
microsoft...=micros~1

--------------------
keeping it simple...

avalekya
Starting Member

7 Posts

Posted - 2006-03-07 : 05:24:26

Hi All,

I restored the database at last.The problem was not with the query or anythin related.

The thing is

RESTORE FILELISTONLY FROM DISK = 'C:\eng1'
shows the physical file path from where the database was backed up in my case the path of SQL Server in my friends machine D:

But in my machine SQL Server is located in C: so all i had to do during restore was to change the physical destination for ldf and mdf files as my SQL Server's path c:\programfiles\.... in the Restore wizard.

Thank u,
avalekya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources