I have two servers, Prod and Test, both running SQL Server 2008 RTM.
Is it possible to have a PowerShell- or VBScript that could copy a database from one server to another?
The copy should of course include all contents of the database, with the exception of privileges so I don't lock myself out from editing the destination.
The destination database should be cleared/reset or completely overwritten, so both source and destination are identical.
Additionally, the connection to the source should be read-only and (if possible) only able to initiate the copy process without actually having access to the data.
I am slightly familiar with PowerShell, so if this only means connecting and starting a task it should be doable. Or do I have to look for advanced solutions?