0

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.

AlexT
  • 123
  • 4
  • 1
    The disk clause of a RESTORE command takes UNC paths, and drive mappings tend to get confused since they are per-user and not per-machine. (IOW, the X: that you see might not be the X: that the SQL instance sees if it sees one at all.) So, rather than "DISK = 'X:\MySiteDB.bak'", you can use "DISK = '\\PRODMACHINE\BACKUPSHARE\MySiteDB.bak'". This presumes that your SQL instances are domain accounts, running with enough read/write permissions on those files and shares. Also, don't share out the root of your C:, make a folder to put the backups into and share that to minimize the attack surface. – Darin Strait Jul 11 '13 at 00:58

1 Answers1

2

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%"
Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
  • Oh, and you should be able to run this from any machine that's in the same domain as the servers, assuming your AD account has SQL and share credentials to do so. – Katherine Villyard Jul 10 '13 at 21:41
  • Depending on how big the BAK is it may make more sense to run this directly on the source server (just to prevent dragging the file across the LAN to the client, only to push it back up to the destination server). – Evan Anderson Jul 10 '13 at 21:47
  • I usually do, but it's not required by the script. I tweaked this from something that I've run from both source and destination servers. – Katherine Villyard Jul 10 '13 at 22:15
  • +1 - I dumped my answer in favor of yours. If I were deploying the batch file I'd probably include the backup and restore scripts in the batch file to be written to temporary files and executed by `sqlcmd` (cursing all the while that CMD.EXE doesn't have "here documents" like any good *nix shell), and I'd probably bring in WMIC to perform the copy process on the source server (in lieu of having an external dependency on `psexec`) if the script wasn't running on the source server to begin with. – Evan Anderson Jul 11 '13 at 04:41