Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

restoring a dat file to msde

Posted on 2006-04-20
31
Medium Priority
?
495 Views
Last Modified: 2008-02-01
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.

0
Comment
Question by:mhabtab2
  • 13
  • 11
  • 6
  • +1
31 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16497074
did u mention the servername

Osql -S serverName -Usa -Ppassword
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16497081
run:
osql /?

there is one more parameter needs
 [-S server]
What is your MSDE sql server name?
check in windows services - mssql..service...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16497083
>[Shared Memory]SQL Server does not exist or access denied.
is the sql server (msde) service started?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mhabtab2
ID: 16497199
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16497232
some firewall ?
some non-default port used?
is the server (the sql computer) really named MSSQL?

0
 

Author Comment

by:mhabtab2
ID: 16497368
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16497409
>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
 

Author Comment

by:mhabtab2
ID: 16497428
Yes I use MSDE 2000 sp4. The dat file is a backup file.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16497509
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
 

Author Comment

by:mhabtab2
ID: 16497541
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16497571
in the osql, you have to put a GO (+newline) to get the command(s) above to be executed.
0
 

Author Comment

by:mhabtab2
ID: 16497634
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16497755
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16497855
download and use  FREE MSDE Query Tool
http://msde.biz/msdequery/download.htm
0
 

Author Comment

by:mhabtab2
ID: 16498068
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16498418
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16498432
or
make sure you have folder(s)
C:\MSSQL\MSSQL\data\
0
 

Author Comment

by:mhabtab2
ID: 16498492
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16498568
did you See the SQL Server error log for more details?

did you try 'with move'


0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 16498615
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
 

Author Comment

by:mhabtab2
ID: 16505144
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16505155
what is the output of this:
RESTORE FILELISTONLY FROM DISK=N'c:\resume.dat'
0
 

Author Comment

by:mhabtab2
ID: 16505226
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16505241
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
 

Author Comment

by:mhabtab2
ID: 16505384
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16505460
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
 

Author Comment

by:mhabtab2
ID: 16505569
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16505586
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
 

Author Comment

by:mhabtab2
ID: 16505631
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16505665
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
 

Author Comment

by:mhabtab2
ID: 16505678
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question