Copy Database Wizard - 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

General SQL Server Forums

New to SQL Server Administration

Copy Database Wizard

Author

Topic

Stugol
Starting Member

3 Posts

Posted - 2009-06-10 : 09:41:38

Has anyone, anywhere, ever, in the whole of creation, since the DAWN OF TIME, managed to get the Copy Database Wizard to actually WORK???????

"The job failed. Check the event log on the destination server for details."

What event log? If I tell it to output a log to a text file, it DOESN'T. If I check the Windows Event Log, I see nothing helpful.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-10 : 10:02:52

Never used it to be honest...I always do backup/restore instead. Why not use this method?

- Lumbago

Stugol
Starting Member

3 Posts

Posted - 2009-06-12 : 14:22:26

Frankly, because if I do, then after the restore has taken place, I have to delete ALL the login accounts for the restored database, then get Properties on all the login accounts in the Security node, and re-assign them to the database roles.

Every bloody time.

This is because SQL Server considers a login account "DBGUI" on one instance of SQL Server to be different to a login account "DBGUI" on the other instance, even though the username, password and EVERYTHING ELSE is identical. So the restored database ends up containing login accounts with the same names as the ones previously set up in the destination database, but COMPLETELY NONFUNCTIONAL, until I delete them and re-assign the existing destination logins to the restored database.

I hope this makes sense. If not, I can post screenshots to clarify.

Is there a quicker solution?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-06-14 : 11:23:29

You will have to Synchronize database users to login everytime you restore databases.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-15 : 03:59:13

It's actually quite easy to automate this...to copy logins with passwords from one server to another you can use the procedures here: http://support.microsoft.com/kb/918992. If the logins are already present on the other server but are orphaned due to the restore you can use this script (remove the try/catch inside the while loop to see the errors for the users that are not unorphaned):

DECLARE 
  @SQL VARCHAR(1000),
  @SuccessCount INT,
  @ErrorCount INT

SET @SuccessCount = 0
SET @ErrorCount = 0
 
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', 'sys', 'INFORMATION_SCHEMA')
 
OPEN curSQL
 
FETCH curSQL INTO @SQL
 
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    EXEC (@SQL)
    SET @SuccessCount = @SuccessCount + 1
  END TRY
  BEGIN CATCH
    SET @ErrorCount = @ErrorCount + 1
  END CATCH

  FETCH curSQL INTO @SQL
END
 
CLOSE curSQL
DEALLOCATE curSQL

RAISERROR (N'%d of the Logins were unorphaned successfully, %d logins don''t exist in the current master database', 1, 1, @SuccessCount, @ErrorCount)

- Lumbago

Stugol
Starting Member

3 Posts

Posted - 2009-06-19 : 07:54:50

Thanks very much. I will try this and see if it works for me.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-06-19 : 11:01:58

quote:
Originally posted by Stugol

Has anyone, anywhere, ever, in the whole of creation, since the DAWN OF TIME, managed to get the Copy Database Wizard to actually WORK???????

Of course.
...in MSSQL 2000 using Enterprise Manager.
In MSSQL 2005 Management Studio it is totally F'd. Makes me want to travel to Redmond and start slapping people.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources