Moving Data Across Servers - SQL Server Forums

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

Import/Export (DTS) and Replication (2000)

Moving Data Across Servers

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-12-17 : 16:13:29

I dont have much experience moving databases. I've always hated doing it because something always seems to mess up for me, and its time I have to do it again.

I am attempting to export the database across servers, however I am getting errors on random stored procedures that can't be moved over. I drop the procedure and recreate it, and it seems to make the DTS package error out on a different error next time. I can't keep doing this one by one because it will take forever.

Any ideas what the problem could be ?? Also, what happens if I export a live database? Updates would be taking place regularly on the source data base, as it would be exporting data.

Thanks for any help

Mike123

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-17 : 16:15:01

Why don't up do a dump and restore?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 16:32:42

Yes, do a backup and a restore. You'll get a snapshot of the committed data at the time that the backup occurs, so you don't have to worry about updates taking place at the same time. Do you need to copy the DTS packages as well? If so, you can just save them to the other server by doing a save as.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-12-17 : 18:03:31

It sounds like I definatley should do a backup / restore. I was having problems with it, so I tried another way. I tried again to find the error and here it is.

As soon as I try to run my webapp it gives the following message

System.Data.SqlClient.SqlException: Could not find stored procedure 'select_autoLogon_GUID'.

I do not understand why it can't find the stored proc. This is the first stored proc that runs upon loading the web app. The logins and database users seem to be in check.

Any ideas???

Thanks alot

mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 18:05:02

Well you didn't move over the stored procedure. A stored procedure is not data. It is an object. To get data and objects, BACKUP/RESTORE is the easiest method. If you just want objects, you can script them out using the generate SQL script wizard. For data, you can use DTS, bcp T-SQL across databases and even linked servers.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-12-17 : 18:27:37

I didnt explain that properly, let me go again here.

I am actually getting this error after doing the backup/ restore method, and I see the SP when looking into the database thru enterprise manager.

Wierd. Any idea?

Thanks again,

Mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 18:31:38

After you performed the restore, did you unorphan the application accounts using sp_change_users_login? Can you execute the stored procedure from Query Analyzer using the account that the application uses to connect to the database server? Is the application pointing to the correct database? The point is that if the stored procedure exists in the source database where the backup was performed, then it exists in the restored database, so something else is wrong. Start with Query Analyzer after you have unorphaned the accounts.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-12-17 : 18:48:54

Tara, thanks for your patience :)

I did not unorphan any accounts. I am not familiar with this, this must be my mistake, I'll look up how to do this unless you have some tips, and no I can't execute the Sp from QA either. Yes the application is definately pointing to the correct db.

thanks again
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 19:02:51

Chad has a script for the unorphan in the script section of SQLTeam.com. Here's the one that I wrote and use:



DECLARE @SQL VARCHAR(100)

DECLARE curSQL CURSOR FOR
	SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name + ''', ''' + name + ''''
	FROM sysusers
	WHERE issqluser = 1 AND name NOT IN ('guest', 'dbo')

OPEN curSQL

FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC (@SQL)
	FETCH curSQL INTO @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

GO

Before you run the code, make sure you are pointing to the correct database in QA. It wouldn't hurt anything if you weren't, but it also wouldn't fix anything unless you are pointing to a database with orphaned accounts.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 19:04:14

Here is the link to his:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-12-19 : 17:45:28

quote:
Originally posted by tduggan

After you performed the restore, did you unorphan the application accounts using sp_change_users_login? Can you execute the stored procedure from Query Analyzer using the account that the application uses to connect to the database server? Is the application pointing to the correct database? The point is that if the stored procedure exists in the source database where the backup was performed, then it exists in the restored database, so something else is wrong. Start with Query Analyzer after you have unorphaned the accounts.

Tara

I'm still having problems with this one...

After I restored, I ran your sproc, but it returned a message saying 0 accounts affected. The part that I find the strangest is that I can connect to the database using Query Analyzer and I can run SQL statements such as 'SELECT * FROM TABLE', however when I try to run a Stored Proc it says it can not find the procedure.

Any other suggestions are greatly appreciated.

Thanks once again

mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-27 : 20:52:56

Check sysobjects to see if the object exists and who the owner is. If you did a BACKUP/RESTORE, then the database will be an exact copy of your source.

SELECT *
FROM DBName.dbo.sysobjects
WHERE type = 'P'

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources