Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
why you don't do a sql JOB that backup and restore your database ? –  KiKine May 31 '13 at 8:51
    
How large is the database? If it's particularly big then backup/restore will be the most efficient way to do this. –  Stuart Moore May 31 '13 at 9:57
    
@KiKine, I am not too familiar with MSSQL, hence my question. If it is possible to have a job that is remotely executed on demand, them I am all for it. –  Trollbane Jun 4 '13 at 1:18
add comment

5 Answers

up vote 0 down vote accepted

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 :

  1. 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.
  2. use PowerShell to do the job.

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.

share|improve this answer
add comment

Quick answer is that this is possible but not straight forward and doing so in read only mode and w/o being able to read data is going to be challenging.

If source database is running then you just can’t copy it because SQL Server will prevent you to do so. This means that you’ll either have to stop SQL Server service or connect to SQL Server to create a backup. In either case you’ll need a level of privileges that can probably access the data.

What I would do is:

  • Create VB Script to backup database
  • Create bat script to zip–copy–unzip database backup
  • Create another VB Script on destination server to restore database backup
share|improve this answer
1  
You'll also need to sort out the permissions, if the requirements are different between prod & dev. But that's straightforward, assuming you have the appropriate rights on the dev server. –  Thomas Rushton Jun 6 '13 at 11:38
    
This means that you’ll either have to stop SQL Server service or connect to SQL Server to create a backup. is totally incorrect point. why would you stop sql server service to take a backup ? –  Kin Jun 22 '13 at 4:00
add comment

As to the person kicking off the job not having access to the data, there's a stored procedure--sp_start_job--in 2005 and higher. The person kicking off the job would only need the one of the SQL Agent roles granted to them, such as SQLAgentOperatorRole.

http://msdn.microsoft.com/en-us/library/ms186757.aspx

share|improve this answer
add comment

I have a Similar Problem. I have production database based in Kenya. Everyday, I have to do following manually: 1)back-up database. 2)Shrink and zip database 3)Use FTP to transfer my database to another server in UK.

How can I be able to make a copy of database in Kenya to server in UK?

share|improve this answer
2  
Ask a new question instead of adding an 'answer' here please! –  Paul White Jun 28 '13 at 6:10
add comment

We created a SQL job to do that. Basically it has 4 steps:

  1. Backup the database to a shared network folder
  2. Drop the test database
  3. Restore the backup taken in step 1 as the test database
  4. Delete the backup

On nights that we want to do the copy we just tick the box in SQL Server Agent Scheduler and go home. Come in the next morning and it's all done.

We normally do it at night because that's when the server is at its quietest. There is no reason that you can't do it during normal operations - none of the steps requires taking the live database offline. But we found that the CPU time and network traffic made it more favourable to do it out of business hours.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.