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.