Background: freshly installed SQL Server 2012 SP3 on Windows Server 2012R2.
Problem: When I use the Copy Database Wizard to copy a database from server2 to server2 I receive:
While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Login timeout expired).
Yes, I use the wizard to make a database copy on the same server. (Accounting software and testing purpose.) I receive this error when the wizard tries to create the Integration Services package. Also what's important, it happens when I use SMSS from my computer. I'm a sysadmin on this SQL Server. I even added my user to this Windows Server's admin group.
We've got an old server1, which is configured in very similar way as server2. I checked every setting that came to my mind and both configs looks the same. No proxies, no users in SSIS Package Execution.
I can copy a database from server1 to server1 and from server2 to server1 without any problem.
It looks like I don't have sufficient right to location: server2_server2\DTS Packages\Copy Database Wizard Packages whatever it is.
Theoretically I could use RDP to server and domain admin account every time but that's pain in the… back. Also there's accounting software support that has high SQL rights but can't have domain admin.
I googled this and usually it's about SQL Server 2008 and running some custom packages.
Best Answer
Community wiki answer:
Use backup and restore instead.
You can write dynamic SQL to do what you said, or just use Copy-DbaDatabase in the PowerShell dbatools to fully automate the task.