June 11, 2009 at 2:29 pm
Hello All!
I used to have a DTS package to accomplish this but have since upgraded to SQL 2005. Yeah, the DTS package is gone...
I need to take a database backup on my primary server, send it to my secondary server, and restore it to STANDBY, for replication purposes. The SSMS Copy Database task can copy the database, but to my knowledge, it cannot restore WITH STANDBY.
I tried using BIDS also. The Transfer Database Task does what I need, but again, no STANDBY. And the CREATE LOGIN statement keeps telling me that either the login is invalid or I do not have permission to add it. I am an Admin on the database server and in SQL Server, and I run the task using my Admin login, not the service account.
Has anyone restored WITH STANDBY on a secondary server and can offer suggestions?
Thanks!
Jason
June 11, 2009 at 3:06 pm
It's me again...
Okay, once I get the database to the secondary site, I can restore the database to STANDBY pretty easily. So forget that part, and let's focus on the Copy Database task.
What causes the CREATE LOGIN to say the database login is invalid when it tries to restore to the target server? I only have problems with SQL, not Windows, logins. Could I be missing security permissions here, or is there a hard, fast rule surrounding SQL login creation?
I'm still reading white papers and articles for the answer... Is someone out there in SQL Server Central Land faster?!?
Jason
June 11, 2009 at 9:07 pm
Hello Jason,
Sorry for asking, but just to be sure, the target server does have Mixed Authentication Mode turned on?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 12, 2009 at 8:25 am
John,
Thanks for replying. I targeted three server in total, and only one of the three had Windows only authentication. The other two had SQL and Windows authentication.
I try to exclude Logins when I create the Copy Database Task package using SSMS, but the darn things still pull in at destination. I will keep playing with different package configurations...
TKS!
Jason
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply