Powershell – Backing up database fully from remote server, and restoring to another remote server

batch-filepowershellsql-server-2008

I currently have three copies of my website (Live, Staging, and Development), with the database for each on three separate servers. More often not, a bug will appear that requires an up-to-date database, so I'll RDP into the Live DB, go into SQL Server Management Studio and back up the database. From there, I'll copy the .bak files to the staging db and dev db servers using RDP and will manually overwrite each db.

It works, but it's a very manual process. Obviously, what I do using the UI can be done using a command like so:

BACKUP DATABASE MySiteDB
TO DISK = 'c:\MySiteDB.bak'
  WITH FORMAT,
    MEDIANAME = 'MySiteDB',
    NAME = 'Live backup'
GO

I could also restore it, using a command like so:

RESTORE DATABASE MySiteDB
FROM DISK = 'X:\MySiteDB.bak'
WITH
    REPLACE,
    STATS = 10, -- Show progress (every 10%)
MOVE 'MySiteDB_Data' TO 'C:\MSSQL\DATA\MySiteDB.mdf', 
MOVE 'MySiteDB_Log' TO 'C:\MSSQL\DATA\MySiteDB.ldf'
GO

However, this would still require me to log into each server, and to copy the file over manually.

Ideally, I would like to be able to run a batch file on my dev server that will create a backup on my live db server, copy it to a network drive on the staging db server (Let's call it X:\) and then take the full backup and overwrite the existing MySiteDB database on staging (and then possibly restore to dev server after). This way, syncing the staging database to live is a one-step process.

I've tried to look into how this could be done and the closest thing I can find is to create a link between the databases. However, I also found the EXEC AT command. I'm not sure if they're the same, but it looks like I could do what I want on here too, according to this article.

That being said, I still can't understand how I'd merge all of these queries into a single query that can be run from a single server. Is it just a matter of using EXEC AT to chain these commands together? Also, how does the server linking work? Can I create a temporary link using the command?

Any help would be appreciated.

Best Answer

You should be able to do this with batch and sqlcmd. Assuming you have backup and restore scripts that write to shares on the servers:

@ECHO ON

set backupscript=c:\path\backup.sql
set restorescript=c:\path\restore.sql

set livestore=\\server\share
set stagestore=\\server\share
set devstore=\\server\share

set liveserver=server\inst
set stageserver=server\inst
set devserver=server\inst


sqlcmd -E -S%liveserver% -i "%backupscript%"
copy "%livestore%\*" %stagestore%
copy "%livestore%\*" %devstore%
sqlcmd -E -S%stageserver% -i "%restorescript%"
sqlcmd -E -S%devserver% -i "%restorescript%"
Related Topic