|
|
You're in the
sql server programming
group:How to transfer from SQL Server Client to MSDE?
sql server programming:
My coworker has SQL Server 2000 client and SQL Server 2000 server installed
and I only have MSDE installed. While he was able to use his GUI client to connect to my MSDE, we could not figure how how to transfer his database to to my instance of MSDE. We tried deleting my database and dragging his database to my instance. That did not work. We tried exporting it as a SQL script, MSAcess and Excel file and in all cases, it only recorded the table name and headings and not the contents of the tables. (1) What were we doing wrong? He created a database backup in a file with the "bak" extenstion but we could not figure out how to restore it on my machine. This was very confusing because the SQL server 2000 client on his machine (while connected to my instance of MSDE) kept wanting the source "device". We gave it the directory the "bak" file was in but it did not like that. When we gave it the full file name of the "bak" file name (unfortunately he was driving and I could not write down the exact error message) it complained about multiple devices. (2) What were we doing wrong here? Now it is the weekend and I don't have access to my coworkers SQL server client, only the "bak" file and my instance of MSDE. (3) Is there a way I can restore the "bak" file without access to a SQL Server client and only access to my instance of MSDE? Perhaps I could write a perl script to do this? Thanks, Siegfried
It is certainly possible to restore, but you need to take the time to read about the RESTORE
DATABASE command. Pay special attention to the MOVE option. If you still fail, you can post the error message and the RESTORE DATABASE command you executed. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Siegfried Heintze" <siegfried@heintze.com> wrote in message
news:uyn6iO92HHA.3760@TK2MSFTNGP03.phx.gbl... > My coworker has SQL Server 2000 client and SQL Server 2000 server installed and I only have MSDE > installed. While he was able to use his GUI client to connect to my MSDE, we could not figure how > how to transfer his database to to my instance of MSDE. We tried deleting my database and dragging > his database to my instance. That did not work. > > We tried exporting it as a SQL script, MSAcess and Excel file and in all cases, it only recorded > the table name and headings and not the contents of the tables. > (1) What were we doing wrong? > > He created a database backup in a file with the "bak" extenstion but we could not figure out how > to restore it on my machine. This was very confusing because the SQL server 2000 client on his > machine (while connected to my instance of MSDE) kept wanting the source "device". We gave it the > directory the "bak" file was in but it did not like that. When we gave it the full file name of > the "bak" file name (unfortunately he was driving and I could not write down the exact error > message) it complained about multiple devices. > (2) What were we doing wrong here? > > Now it is the weekend and I don't have access to my coworkers SQL server client, only the "bak" > file and my instance of MSDE. > > (3) Is there a way I can restore the "bak" file without access to a SQL Server client and only > access to my instance of MSDE? Perhaps I could write a perl script to do this? > > Thanks, > Siegfried >
Is it possible to restore the "bak" file to my MSDE without the SQL Server
Client Interface? I assume I would have to write a program for that. Does anyone have a sample? Thanks, Siegfried "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:uJgawG$2HHA.4584@TK2MSFTNGP03.phx.gbl... [quoted text, click to view] > It is certainly possible to restore, but you need to take the time to read
> about the RESTORE DATABASE command. Pay special attention to the MOVE > option. If you still fail, you can post the error message and the RESTORE > DATABASE command you executed. >
I'm google searching for OSQL.EXE and trying to run it. I'm not finding it
obvious. Does anyone have any hints how to load a bak file? I tried osql.exe /help with no luck! Siegfried
Please ignore that last message I posted about not knowing where to look for
help. I forgot about books on line. From there I see an example. Here is my attempt: $ osql.exe -U sa -P mypass USE ReadiManager GO USE Master go GO RESTORE DATABASE ReadiManager.bak FROM FILE = 'C:\temp\ReadiManager.bak' GO 1> 2> 1> 2> 1> 1> 2> 3> Msg 170, Level 15, State 1, Server SHEINTZE-XP-HP, Line 1 Line 1: Incorrect syntax near '.'. Hmmm... looks good to me. What is it complaining about? Thanks Siegfried
Sorry but I cannot run that because .NET Framework v2 conflicts with some
other software we inherited. Apparently .NET Framework 1.0 marshalling is not compatible with .NET Framework 2.0 and the latter messes up our software (so I am told). Is osql.exe the only other option? Thanks, Siegfried
No need to write a program. Use OSQL.EXE, it comes with MSDE and it is a command-line interface to
SQL Server/MSDE. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Siegfried Heintze" <siegfried@heintze.com> wrote in message
news:e3rEbeD3HHA.4476@TK2MSFTNGP06.phx.gbl... > Is it possible to restore the "bak" file to my MSDE without the SQL Server Client Interface? > I assume I would have to write a program for that. Does anyone have a sample? > Thanks, > Siegfried > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message > news:uJgawG$2HHA.4584@TK2MSFTNGP03.phx.gbl... >> It is certainly possible to restore, but you need to take the time to read about the RESTORE >> DATABASE command. Pay special attention to the MOVE option. If you still fail, you can post the >> error message and the RESTORE DATABASE command you executed. >> >
On Aug 11, 12:49 pm, "Siegfried Heintze" <siegfr...@heintze.com>
[quoted text, click to view] wrote: > Is it possible to restore the "bak" file to my MSDE without the SQL Server > Client Interface? > I assume I would have to write a program for that. Does anyone have a > sample? Download SQL Server Management Studio Express. It provides quite a bit of the functionality of SQL Server Enterprise Manager, works with SQL Server versions up to 2005, all editions including MSDE, and is free.
My guess is that you want:
RESTORE DATABASE ReadiManager FROM FILE = 'C:\temp\ReadiManager.bak' Note, no .bak for the database that you will create. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Siegfried Heintze" <siegfried@heintze.com> wrote in message
news:eKhpS5G3HHA.5756@TK2MSFTNGP04.phx.gbl... > Please ignore that last message I posted about not knowing where to look for help. > > I forgot about books on line. > > From there I see an example. Here is my attempt: > > $ osql.exe -U sa -P mypass > USE ReadiManager > GO > USE Master > go > GO > RESTORE DATABASE ReadiManager.bak > FROM FILE = 'C:\temp\ReadiManager.bak' > GO > 1> 2> 1> 2> 1> 1> 2> 3> Msg 170, Level 15, State 1, Server SHEINTZE-XP-HP, Line 1 > Line 1: Incorrect syntax near '.'. > > > Hmmm... looks good to me. What is it complaining about? > > Thanks > Siegfried >
Of course!
OK, I tried again with the recommended text. What am I doing wrong? Thanks, Siegfried $ osql.exe -U sa -P xyz RESTORE DATABASE ReadiManager FROM FILE = 'C:\temp\ReadiManager.bak' GO 1> 2> 3> 4> Msg 156, Level 15, State 1, Server SHEINTZE-XP-HP, Line 2 Incorrect syntax near the keyword 'FILE'.
This error message looks familiar. We were getting it when trying to use the
SQL Enterprise client GUI. I did a google search and $ osql.exe -U sa -P xyz RESTORE DATABASE ReadiManager FROM DISK = 'C:\temp\ReadiManager.bak' GO 1> 2> 3> Msg 3205, Level 16, State 2, Server SHEINTZE-XP-HP, Line 1 Too many backup devices specified for backup or restore; only 64 are allowed. Msg 3013, Level 16, State 1, Server SHEINTZE-XP-HP, Line 1 RESTORE DATABASE is terminating abnormally. I did a google search and tried to follow the directions at http://www.killersites.com/mvnforum/mvnforum/viewthread?thread=2240. I tried running the stored command 'exec usp_killprocess "ReadiManager"'. I did not get any errors but it did not seem to help either! I don't understand http://support.microsoft.com/default.aspx?scid=kb;en-us;314546. Do I need to use a "WITH MOVE" statement? Thanks, Siegfried
Change FILE = to DISK =
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Siegfried Heintze" <siegfried@heintze.com> wrote in message
news:OFlJQWS3HHA.5116@TK2MSFTNGP04.phx.gbl... > Of course! > > OK, I tried again with the recommended text. What am I doing wrong? > > Thanks, > Siegfried > > > > $ osql.exe -U sa -P xyz > RESTORE DATABASE ReadiManager > FROM FILE = 'C:\temp\ReadiManager.bak' > > GO > 1> 2> 3> 4> Msg 156, Level 15, State 1, Server SHEINTZE-XP-HP, Line 2 > Incorrect syntax near the keyword 'FILE'. > >
A quick Google search gave me some candidates for your error message (3205). I'd start with:
http://support.microsoft.com/kb/833710 But also look at: http://support.versionone.net/kb/article.aspx?id=10415 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Siegfried Heintze" <siegfried@heintze.com> wrote in message
news:e4QgxXU3HHA.2752@TK2MSFTNGP06.phx.gbl... > This error message looks familiar. We were getting it when trying to use the SQL Enterprise client > GUI. > I did a google search and > $ osql.exe -U sa -P xyz > RESTORE DATABASE ReadiManager > FROM DISK = 'C:\temp\ReadiManager.bak' > GO > 1> 2> 3> Msg 3205, Level 16, State 2, Server SHEINTZE-XP-HP, Line 1 > Too many backup devices specified for backup or restore; only 64 are allowed. > Msg 3013, Level 16, State 1, Server SHEINTZE-XP-HP, Line 1 > RESTORE DATABASE is terminating abnormally. > > I did a google search and tried to follow the directions at > http://www.killersites.com/mvnforum/mvnforum/viewthread?thread=2240. I tried running the stored > command 'exec usp_killprocess "ReadiManager"'. I did not get any errors but it did not seem to > help either! > > I don't understand http://support.microsoft.com/default.aspx?scid=kb;en-us;314546. Do I need to > use a "WITH MOVE" statement? > > Thanks, > Siegfried >
[quoted text, click to view]
On Aug 11, 7:21 pm, "Siegfried Heintze" <siegfr...@heintze.com> wrote: > Sorry but I cannot run that because .NET Framework v2 conflicts with some > other software we inherited. Apparently .NET Framework 1.0 marshalling is > not compatible with .NET Framework 2.0 and the latter messes up our software > (so I am told). > > Is osql.exe the only other option? > > Thanks, > Siegfried Install SSMSE on some other machine where NET 1.0 isn't a problem, and connect to the server over the network.
On Sat, 11 Aug 2007 17:21:15 -0600, "Siegfried Heintze"
[quoted text, click to view] <siegfried@heintze.com> wrote: >Sorry but I cannot run that because .NET Framework v2 conflicts with some >other software we inherited. Apparently .NET Framework 1.0 marshalling is >not compatible with .NET Framework 2.0 and the latter messes up our software >(so I am told). > >Is osql.exe the only other option? > >Thanks, >Siegfried Add another machine to your network and put SQL Server Express on it? I'm baffled as to why you'd use MSDE when you can can have SQL server express instead. Most people hate MSDE. Are there not options in the SQL RESTORE database command? You almost certainly need to use the MOVE option. Does something like this work? restore database DatabaseName from disk = 'c:\DatabaseName.bak' with move 'DatabaseNameData' to 'd:\DatabaseNameData.mdf', move 'DatabaseNameLog' to 'd:\DatabaseNameLog.ldf', replace - That replace option is optional. You only need replace if you want to overwrite what's already there. If both you and your collegue use identical paths for you database and backup locations I think you can even dispense with the MOVE - although
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |