restoring a dat file to msde

Hi experts,

I try to restore a .dat file to an msde database. I try to do it from the following path:

c:\Program Files\Microsoft SQL Server\80\Tools\Binn

Then I give the following command to log in

osql –Usa –Ppassword.

Than I get the following error message.  

[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).

I have tried different thins but it doesn't help.

mhabtab2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
did u mention the servername

Osql -S serverName -Usa -Ppassword
0
Eugene ZCommented:
run:
osql /?

there is one more parameter needs
 [-S server]
What is your MSDE sql server name?
check in windows services - mssql..service...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>[Shared Memory]SQL Server does not exist or access denied.
is the sql server (msde) service started?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mhabtab2Author Commented:
The msde service is started. When I try the following

osql -S MSSQL$DB2000 -Usa -Ppassword

I get the following error

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>osql -SMSSQL$DB2000 -Usa -Ppassword
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
some firewall ?
some non-default port used?
is the server (the sql computer) really named MSSQL?

0
mhabtab2Author Commented:
Ok now I have added my computer name before the server name and now I come in osql command window. Which command do I have to give in order to restore the .dat file?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I try to restore a .dat file to an msde database.

what is this .dat file? a backup file, a data file (SQL 6.5), or what?

I guess that you are currently using the MSDE 2000 version?
0
mhabtab2Author Commented:
Yes I use MSDE 2000 sp4. The dat file is a backup file.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
RESTORE DATABASE database_name FROM FILE='c:\backup\yourfile.dat' WITH REPLACE

if that fails, post the error message, and run that following:

RESTORE FILELISTONLY FROM FILE='c:\backup\yourfile.dat'
0
mhabtab2Author Commented:
Ok. But how can i check if the restore is succesfull? Because the only thing I see is that it goes to the next line. For example I type the command at line 4. Aft that I enter and than it goes to line 5 without saying succesfull or not.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in the osql, you have to put a GO (+newline) to get the command(s) above to be executed.
0
mhabtab2Author Commented:
When I give the following command:

1> RESTORE DATABASE resume FROM FILE = d:\cursus\resume\resume.dat WITH REPLACE
2> GO

I get the following error message:

Incorrect syntax near the keyword file.

I get also this error message when I give the command:

RESTORE FILELISTONLY FROM FILE='c:\backup\yourfile.dat'

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, the keyword is DISK and not FILE:

RESTORE DATABASE resume FROM DISK = 'd:\cursus\resume\resume.dat' WITH REPLACE
GO

resp:

RESTORE FILELISTONLY FROM DISK='c:\backup\yourfile.dat'
GO
0
Eugene ZCommented:
download and use  FREE MSDE Query Tool
http://msde.biz/msdequery/download.htm
0
mhabtab2Author Commented:
When I try the the code:

RESTORE DATABASE resume FROM DISK = 'd:\cursus\resume\resume.dat' WITH REPLACE
GO

I get the following errors


1> RESTORE DATABASE resume FROM DISK = 'd:\cursus\resume\resume.dat' WITH REPLAC
E
2> GO
Msg 5105, Level 16, State 2, Server NLBRNXP-20551\DB2000, Line 1
Device activation error. The physical file name
'C:\MSSQL\MSSQL\data\Resume_Data.MDF' may be incorrect.
Msg 3156, Level 16, State 1, Server NLBRNXP-20551\DB2000, Line 1
File 'Resume_Data' cannot be restored to 'C:\MSSQL\MSSQL\data\Resume_Data.MDF'.
Use WITH MOVE to identify a valid location for the file.
Msg 5105, Level 16, State 2, Server NLBRNXP-20551\DB2000, Line 1
Device activation error. The physical file name
'C:\MSSQL\MSSQL\data\Resume_Log.LDF' may be incorrect.
Msg 3156, Level 16, State 1, Server NLBRNXP-20551\DB2000, Line 1
File 'Resume_Log' cannot be restored to 'C:\MSSQL\MSSQL\data\Resume_Log.LDF'.
Use WITH MOVE to identify a valid location for the file.
Msg 3013, Level 16, State 1, Server NLBRNXP-20551\DB2000, Line 1
RESTORE DATABASE is terminating abnormally.
1>

0
Eugene ZCommented:
use with move:

example:

RESTORE DATABASE [test]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\admin.bak'
WITH  FILE = 1,
 MOVE N'admin' TO N'C:\admin.mdf',  
MOVE N'admin_log' TO N'C:\admin_log.LDF',  
NOUNLOAD,  REPLACE,  STATS = 10
GO
0
Eugene ZCommented:
or
make sure you have folder(s)
C:\MSSQL\MSSQL\data\
0
mhabtab2Author Commented:
AND I get the following error by giving the above command:

1> RESTORE DATABASE TEST FROM DISK='d:\curus\resume\TEST.dat' WITH REPLACE
2> GO
Msg 3201, Level 16, State 2, Server NLBRNXP-20551\DB2000, Line 1
Cannot open backup device 'd:\curus\resume\TEST.dat'. Device error or device
off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server NLBRNXP-20551\DB2000, Line 1
RESTORE DATABASE is terminating abnormally.
1>
0
Eugene ZCommented:
did you See the SQL Server error log for more details?

did you try 'with move'


0
Eugene ZCommented:
can you run just to see if the backup file is ok?

RESTORE HEADERONLY
FROM DISK = N'd:\curus\resume\TEST.dat'
WITH NOUNLOAD;
GO
0
mhabtab2Author Commented:
When I restore give the headeronly command it gives me the message that one row is affected. That seems to be ok. Look at the code below. But what's next to do?

1> RESTORE HEADERONLY FROM DISK = N'c:\resume.dat' WITH NOUNLOAD;
2> GO
 BackupName


        BackupDescription





                                                BackupType
        ExpirationDate          Compressed Position DeviceType
        UserName


        ServerName


        DatabaseName


        DatabaseVersion DatabaseCreationDate    BackupSize
        FirstLsn                    LastLsn
        CheckpointLsn               DifferentialBaseLsn
        BackupStartDate         BackupFinishDate        SortOrder CodePage
        UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel
        SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor
        SoftwareVersionBuild
        MachineName

                                                                  Flags
        BindingId
        RecoveryForkId
        Collation


 -------------------------------------------------------------------------------

        --------------------------------------------------
        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        --------------------------------------- ----------
        ----------------------- ---------- -------- ----------
        ------------------------------------------------------------------------

        ---------------------------------------------------------
        ------------------------------------------------------------------------

        ---------------------------------------------------------
        ------------------------------------------------------------------------

        ---------------------------------------------------------
        --------------- ----------------------- ----------------------
        --------------------------- ---------------------------
        --------------------------- ---------------------------
        ----------------------- ----------------------- --------- --------
        --------------- ---------------------- ------------------
        ---------------- -------------------- --------------------
        --------------------
        ------------------------------------------------------------------------

        --------------------------------------------------------- -----------
        --------------------------------------
        --------------------------------------
        ------------------------------------------------------------------------

        ---------------------------------------------------------
 Resume backup


        NULL





                                                         1
                           NULL          0        1          2
        CONFERENCEDELL\Administrator


        CONFERENCEDELL


        Resume


                    539 2002-03-02 13:13:48.000                 994816
                   5000000048300001            5000000048900001
                   5000000048700001                           0
        2002-03-02 16:32:05.000 2002-03-02 16:32:06.000        52      228
                   1033                 196609                 80
                    4608                    8                    0
                         384
        CONFERENCEDELL

                                                                            0
        {5E79820D-4C0B-41AD-951A-7A68ABA0B195}
        {5E79820D-4C0B-41AD-951A-7A68ABA0B195}
        SQL_Latin1_General_CP1_CI_AS



(1 row affected)
1>
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the output of this:
RESTORE FILELISTONLY FROM DISK=N'c:\resume.dat'
0
mhabtab2Author Commented:
The output then is:

1> RESTORE FILELISTONLY FROM DISK=N'c:\resume.dat'
2> go
 LogicalName


        PhysicalName





                                                     Type
        FileGroupName


        Size                   MaxSize
 -------------------------------------------------------------------------------

        --------------------------------------------------
        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        ------------------------------------------------------------------------

        -------------------------------------------- ------------------------
        ------------------------------------------------------------------------

        ---------------------------------------------------------
        ---------------------- ----------------------
 Resume_Data


        C:\MSSQL\MSSQL\data\Resume_Data.MDF





                                                     D
        PRIMARY


                       1048576         35184372080640
 Resume_Log


        C:\MSSQL\MSSQL\data\Resume_Log.LDF





                                                     L
        NULL


                       1048576         35184372080640

(2 rows affected)
1>
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to which folder do you want the database files to go?
C:\MSSQL\MSSQL\data\ as the original database?
YES: ensure that folder exists
NO: ensure the folder exits

Put the new database name and the folder path as needed:

RESTORE DATABASE [test_restore]
FROM  DISK =N'c:\resume.dat'
WITH  FILE = 1,
 MOVE N' Resume_Data' TO N'C:\MSSQL\MSSQL\data\Resume_Data.MDF' ,  
MOVE N' Resume_Log' TO N'C:\MSSQL\MSSQL\data\Resume_Log.LDF',  
NOUNLOAD,  REPLACE,  STATS = 10
GO
0
mhabtab2Author Commented:
You wer right. There was noC:\MSSQL\MSSQL\data\ directory. Now I have made the C:\MSSQL\MSSQL\DATA\  directory and when I give the followin command:

RESTORE DATABASE [test_restore]
FROM  DISK =N'c:\resume.dat'
WITH  FILE = 1,
 MOVE N' Resume_Data' TO N'C:\MSSQL\MSSQL\data\Resume_Data.MDF' ,  
MOVE N' Resume_Log' TO N'C:\MSSQL\MSSQL\data\Resume_Log.LDF',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

I get the following output:

Msg 3234, Level 16, State 2, Server NLBRNXP-20551\DB2000, Line 1
Logical file ' Resume_Data' is not part of database 'test_restore'. Use RESTORE
FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Server NLBRNXP-20551\DB2000, Line 1
RESTORE DATABASE is terminating abnormally.
1>
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see that I had a space in the logical file names :

RESTORE DATABASE [test_restore]
FROM  DISK =N'c:\resume.dat'
WITH  FILE = 1,
 MOVE N'Resume_Data' TO N'C:\MSSQL\MSSQL\data\Resume_Data.MDF' ,  
MOVE N'Resume_Log' TO N'C:\MSSQL\MSSQL\data\Resume_Log.LDF',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

0
mhabtab2Author Commented:
THANK YOU VERY MUCH. GREAT. THIS WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!

But how can I open it. In Visual Studio I have made a connection with server explorer to my db2000 database. How can I add the mdf file to to my db000 msde server?

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
by the restore, it IS already attached to the msde server.
the name of the database is test_restore unless you changed the restore statement.
0
mhabtab2Author Commented:
The files that i can find that are restored are:

Resume_Data.MDF
Resume_Log.LDF

These files are in the C:\MSSQL\MSSQL\data\                  directory.

I can't find test_restore while I have used that.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in osql:

select name from master.dbo.sysdatabases
go

this should return in the list the name test_restore
this is the name you should connect to, you don't connect (directly) to the .mdf file ?!

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mhabtab2Author Commented:
HELLO EXPERTS,

I have found it. I have stopped the services sqlagent en sql server. After that I have copied the files Resume_Data.MDF and Resume_Log.LDF to c:\program files\microsoft sql server\msdb$2000\data. Then i started the services again, made a connection in Visual Studio .Net  to the msde database and there was the database test_resume.

AGAIN, THANK YOU VERY MUCH FOR YOUR GREAT HELP!!!!!!!!!!!!!!!!!!!!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.