Restore SQL2k5 database from SQL2k

starstarstarstarstarstarstarstarstarstar Rating: 0/5 (0 vote cast) print
I am trying to restore my SQL2000 database into my SQL2k5 database. I receive an error:


C:Documents and SettingsmackDesktop>SQLCMD.exe -S (local)SQLExpress -E
1> RESTORE DATABASE chk
2> FROM DISK = 'C:chk'
3> WITH MOVE 'CHK_DATA' TO 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATAchk.mdf',
4> MOVE 'CHK_Log TO ' TO 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDA
TAchk.LDF'
5> GO

Msg 3154, Level 16, State 4, Server KEITHSQLEXPRESS, Line 1
The backup set holds a backup of a database other than the existing 'chk' databa
se.
Msg 3013, Level 16, State 1, Server KEITHSQLEXPRESS, Line 1
RESTORE DATABASE is terminating abnormally.
Sqlcmd: Error : Microsoft OLE DB Provider for SQL Server : One or more errors oc
curred during processing of command..
1>

As you see my statement is:
RESTORE DATABASE chk
FROM DISK = 'C:chk'
WITH MOVE 'CHK_DATA' TO 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAchk.mdf',
MOVE 'CHK_Log TO ' TO 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAchk.LDF'
GO

What am I doing wrong? On my webhost with the SQL2k, I have recreated the database on a different user so that it didn't back up ALL my databases I have.
 : mack     Reply  

Replies (16)

I got it. Thank you anyhow.

: mack    Reply

profile
KINGSLEY TAGBO

Could you please post your final solution, how you solved it?Thanks


Instead of using my webhost's back up feature, I backed up my database using Query Analyser (wrote the BACKUP database command for the LOG and DATA and made them avaliable on my FTP). I then used this: RESTORE DATABASE test FROM DISK = 'C:db.bak' WITH MOVE 'sidra_Data' TO 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAchk.mdf', MOVE 'sidra_Log' TO 'C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAchk.LDF' GO It all worked well, but now I can't connect to the database using web-based applications. Or any application for that matter. I was able to use SQL Manager (Preview 1) but now that don't even work. [:'(].. any ideas?

: mack    Reply

profile
KINGSLEY TAGBO

It looks like your webhost is webhost4life[:O], anyway,do you still have the same logins with the same permissions in the database? For Example, if a login 'aspnet' had execute permissions in database 'webhost4life', does that same login have the same permission now?Thanks


Also review that you are using an SQLConnection, may be if you are using OLEDBconnection, the OLEDBConnection may be is not compatible yet with SQL Yukon.

: NightHawk    Reply

Yes, my webhost IS webhost4life.com. But now I have a dedicated server I'm setting this up on.

I followed the configuration on: http://www.kdkeys.net/restore-sql2k5-database-from-sql2k/#link-7075
It all worked well. I created the same login, with the permission "sysadmin" (as specified in that tutorial).

Here is the exact error I get:

Microsoft OLE DB Provider for SQL Server error '80004005'

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

/include/sql.asp, line 30

This is my connection string (btw I'm doing this in ASP, and this was a guess sorta:)
Provider=SQLOLEDB;Server=(local)SQLExpress;User ID=mack;password=(HIDDEN);database=chk;Persist Security Info=True

I would think "sysadmin" gets execute permissions, maybe not. If you could, explain how to do this. I am using sp_addsrvrolemember 'mack', 'sysadmin' ATM.

If I have to use completely different code for my database, I'd have to redo all of my programming (it's in one file, still a lot).. plus my forums (WebWiz Forums) will not work anymore.

Thanks

: mack    Reply

Yes--using SQL Manager I can access it just fine.

: mack    Reply

I can't connect to SQL at ALL with anything OTHER then SQL Express Manager (Preview 1). It may be configured wrong, I don't know. I tried using DRIVER={SQL Driver} (or whatever it is I forget now [:|]).. I'm not sure what I've done wrong.. and knowing it is in Beta, it may not be me. I'll keep messing with it though. I'll reboot the server too..

: mack    Reply

Yes! I got it! I had to specify the port, and I also put in the IP address rather then the (local)SQLExpress See for yourself: http://www.kdkeys.net/restore-sql2k5-database-from-sql2k/#link-7076

: mack    Reply

OK I'm back. Now if you go to:
http://www.kdkeys.net/restore-sql2k5-database-from-sql2k/#link-7077
it's fast and all but it gives an error:

ADODB.Recordset error '800a0cb3'

Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.

/forums/forum_topics.asp, line 362


: mack    Reply

If you have the .mdf file, try attach it to database instead restore.

: NightHawk    Reply

When I create the file from my webhost (its webbased thing), the file does not have an extension at all. I'm not sure how to attach the database with SQL2005.

: mack    Reply

Are you shure that u have the user and pwd defined in your SQL Server?

: NightHawk    Reply


Post A Reply

 Questions & Answers