Copy a SQL Server database from one server to another on demand 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?

Thank you.
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? 


You have couple of options :


Use Ola's solution to do backup on PROD server and then schedule the script in SQL Agent job to a. do backup, b. copy the backupfile to test server and c. restore the backup on the test server.
use PowerShell to do the job.


Use PowerShell To Copy A Database To Another Instance
backup and restore your databases using SQL Server PowerShell and SMO. 
Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets




  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 database backup includes all the data in the database when the FULL backup was taken including the database users. When you restore the database on Test server, make sure to SYNC Orphan users.


  The destination database should be cleared/reset or completely overwritten, so both source and destination are identical.


When you restore the database, you have to use REPLACE option to overwrite the files with the new ones.


  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?


You can schedule the script to run at specific time or just run it on demand. Refer to the links that I have suggested above.