Restoring a database from .bak file on another machine

Question

I've not done much SQL and am still pretty new to this, so please excuse what's probably a basic question.

I've been asked to look into creating an SQL job to backup our databases, store the .baks on another machine and then to restore them to a second server. I've been doing a bit of research and playing with SSMS and have back-ed up the database to my personal machine by setting up a share and running a backup job to the share location. I'm now trying to create a new database (on the same server I back-ed up from) by restoring the .bak file (but giving the database I'm trying to create a new name and what-not) but am unable to specify restoring it from the share, like I did when backing it up/I can't find how to specify other network locations and am just browsing the server's C drive when I try to locate the file.

For now, I'm just using the built-in wizards to try and achieve this (open SSMS -> Connect to server -> right click DataBases -> Restore DataBases and then select From Device and browse to find the file).

This isn't the final process, just me trying to get to grips with how this works. As I said, the idea is to ultimately have a scheduled job to backup the DB from server1 to a .bak on, say, my personal machine and then to restore that to a DB on server2 (different network, different city) and, probably, with a series of SQL commands rather than using the wizard every time (there are a few DBs that'll, ultimately, need backing up).

My apologies for the, possibly, quite drawn out and convoluted question - essentially, all I need to know is can I/how can I restore a DB in SSMS from a .bak on a different machine? Many thanks

Answer 1

You could use something like the following script. It restores a database from the filesystem, and it overwrites the existing database with the name of "MyDB", moving the files to new locations of your choice in the process.

RESTORE DATABASE
    MyDB
FROM DISK = '\\MyShare\MyBackup.bak'
WITH 
    MOVE 'DataFile' TO 'D:\myNewDBLocation\DataFile.mdf',
    MOVE 'LogFile' TO 'E:\\myNewDBLocation\LogFile.ldf'
, REPLACE

You can find out the name of the llogical files (in the above, those are called DataFile and LogFile by running the following:

RESTORE FILELISTONLY 
FROM DISK = '\\MyShare\MyBackup.bak'

Additional information about various options and parameters:

RESTORE (Transact-SQL)