Backup to another machine, help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Backup to another machine, help!

Author

Topic

elektrobank
Starting Member

15 Posts

Posted - 2003-02-27 : 20:52:42

I added the option in my program to backup my database using the
following command:

BACKUP DATABASE db_name TO DISK = filename WITH INIT

When I restore it to the same machine it works fine, but when I
restore it to a different machine, the restore works but I can't log
into the database anymore, it gives me a message saying Login Fails.

I am using the following command:

RESTORE DATABASE db_name FROM DISK = filename

My database has a user account on it, I'm gussing that might have
something to do with it. What exactly do I have to do in my backup
and restore to allow me to restore the database to other machines?

Thanks...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-27 : 20:55:16

Are you logging in as sa?
Sounds like you have lost the mappnig between your login and the database user and will have to re-establish it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elektrobank
Starting Member

15 Posts

Posted - 2003-02-27 : 20:57:13

No, I'm logging in through an account I created.

quote:

Are you logging in as sa?
Sounds like you have lost the mappnig between your login and the database user and will have to re-establish it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-27 : 22:20:08

Have you also created a user in the database for that login after the restore?

Try it with sa.
Also look at the database status but that's probably OK.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elektrobank
Starting Member

15 Posts

Posted - 2003-02-27 : 23:06:08

The user is already there when I do the restore. I tried it with sa just to see if that would work but it also gave me a login error.

quote:

Have you also created a user in the database for that login after the restore?

Try it with sa.
Also look at the database status but that's probably OK.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-27 : 23:43:21

The user is probably linked to a different logon id on the other server so won't be valid on this one.

Not being able to connect with sa is more serious.
What happens when you do select * from master..sysdatabases
or sp_helpdatabase.

Does the database look ok in enterprise manager?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-02-28 : 05:30:26

This pages has some good information about backing up over a network, it might answer your question.

http://www.swynk.com/faq/sql/sqlfaq_backup.asp#Network

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources