Restoring database problem Urgent. 3

[chrigil]

I have backed up a database from my online hosting company which has left me with a BAK file. I now want to load this create this same DB on my development server in order to do some more work on it but I am having a problem with the BAK file.

I get an error message along the lines of "The database is trying to be created at location E:\Program Files\ etc ". This clearly suggests to me that the file contains absolute paths on recreating the DB and pretty much isn't meant for transferring DB's from one machine to another.
I am unable to connect to my Hosting Company and transfer that way due to their security policy and as such have no way of creating a development server.

The error message does talk about "use MOVE WITH to identify a valid location". What is this and where can i learn how to use it. I've googled for it with no luck???

any information you can offer is appreciated,

Thanks in advance,

Chris

 

[JamesLean]

Look up the RESTORE command in BOL. The basic syntax to move the files from a backup is something like:

RESTORE DATABASE <new database name>
FROM DISK = '<location of backup file>'
WITH MOVE '<logical data file name>' TO '<new data file location>',
	MOVE '<logical log file name>' TO '<new log file location>'

--James

 

[SQLBill]

Here's information on where to find the BOL...

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

-SQLBill

Posting advice: FAQ481-4875

 

[chrigil]

I'm still having problems.

The backed up copy which I now wanna transfer to my development server has the paths beginning E:\ whereas my development server has the paths beginning C:\. I assume that the following CODE would essentially say:

Restore TO database kshsDB
USE 'C:\Program Files\Microsoft SQL Server Data\MSSQL\BACKUP\kshs_14_03_05.bak' as the SOURCE file
Change references to the location 'E:\etc' TO 'C:\etc' for the data files
Change references to the location 'E:\etc' TO 'C:\etc' for the Log files

I am using the following commands:

RESTORE DATABASE kshsDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server Data\MSSQL\BACKUP\kshs_14_03_05.bak'
WITH MOVE 'E:\mssql2000\MSSQL\data\db112996205.mdf' TO 'C:\Program Files\Microsoft SQL Server Data\MSSQL\data\kshsDB_Data.MDF',
    MOVE 'E:\mssql2000\MSSQL\data\db112996205_log.LDF' TO 'C:\Program Files\Microsoft SQL Server Data\MSSQL\data\kshsDB_Log.LDF'

What exactly is going wrong or not being done correctly?

Thanks in advance,

Chris

 

[chrigil]

Sorry I just realised I didn't give you guys and girls much to work with in terms of the error message.

The message I get is:

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'E:\mssql2000\MSSQL\data\db112996205.mdf' is not part of database 'kshsDB'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Surely if I understand what I'm trying to do correctly (ok so I admit its not very likely ;-) thats the whole point. The Path is not part of the database which is why I'm trying to change it.



Thanks in advance,

Chris

 

[SQLBill]

First, where is the backup - on tape or on disk?

Next, you need to use the LOGICAL FILE name not the physical file name in the beginning part of the MOVE statement.

RESTORE DATABASE kshsDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server Data\MSSQL\BACKUP\kshs_14_03_05.bak'
WITH MOVE 'kshsDB' TO 'C:\Program Files\Microsoft SQL Server Data\MSSQL\data\kshsDB_Data.MDF',
    MOVE 'kshsDB_log' TO 'C:\Program Files\Microsoft SQL Server Data\MSSQL\data\kshsDB_Log.LDF'

Again, refer to the BOL for more information. Use the INDEX tab and enter RESTORE DATABASE, then scroll down and select the option for Transact-SQL.


-SQLBill


Posting advice: FAQ481-4875

 

[chrigil]

Thanks for your help, I've managed to get the Database imported but when I used Query Analyser to do a quick check to see if it worked:

SELECT * FROM newsTable

it tells me:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'newsTable'.

If i write:

SELECT * FROM dbo112996205.newsTable

It works ok but this isn't the name that the tables used to be and as such my ASP pages won't work. It'll mean going through the Website changing all the names only to change them all back once the development is over and the production server is used again.

Is there any way I can rename it newsTable instead of dbo112996205.newsTable?



Thanks in advance,

Chris

 

[mrdenny]

Use sp_changeobjectowner to change the object owner from dbo112996205 to dbo. I assume that your username with the hosting company is dbo112996205 which is why it worked before.

Your other option would be to create a user account on your dev server named dbo112996205. Then use that account for the web server to log into the SQL Server with.

To change all the tables owners use a script like this.

sp_MSForEachTable 'sp_changeobjectowner ?, ''dbo'''

I think that will work. If not a cursor will be needed.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chrigil]

I worked out how to do it partly with the help of the MrDenny answer shown above. For future reference:

sp_changeobjectowner 'dbo112996205.myTableName' , 'dbo'

This changes the table called dbo112996205.myTableName to dbo.myTableName.
I think MrDenny's answer basically does the same but iterates through all the tables at once rather than me having to change myTableName each time.

I hope this helps someone (probably me again in 6 months when I forget



Thanks in advance,

Chris

 

[mrdenny]

Yes, my command would have taken care of all tables within the database in a single shot (I'm lazy and don't like typing).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chrigil]

Hi MrDenny, I assumed that your statement would work in the way you suggested but when I tried it it gave me an error along the lines of:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I get 20 of these error messages, which I assume is one for each table as there are 20 tables.
I have done the job, as I mentioned above by doing it manually for each table however for future reference (I have a Problems & Solution word document in which I document this sort of thing) how would I change all the table names at once correctly?

Incidently, is the dbo.mytable part of the table name a NAME or an OWNER as I read some articles suggesting its the latter?

Thanks for your help,

Chris

 

[mrdenny]

dbo is the table owner not part of the name.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

BTW, there is a way to do the "With Move" option in Enterprise Manager for anyone who is interested. Click the Option tab of the Restore Database Window after choosing the device to restore in the first tab. About halfway down is two rows with two boxes each, listing the data name and the path name. Click in the path name box and change it to the correct pathname for where you have the data files and log files saved on your PC.

Sometimes you also have to click "Force Restore Over Existing Database". Especially if you're using a name for a DB that isn't the same as the DB name whose backup you are restoring. Such as changing Accounting to AcctMgmt.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!