Link to home
Start Free TrialLog in
Avatar of pzozulka
pzozulka

asked on

How to restore using a SQL .bak file

One of our external developers sent us a copy of the SQL db so that we can upload it to our AWS sql server. He gave us a .bak file. How do I use this to attach the SQL db on my server?

My end goal is to be able run queries against this database in our environment.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

First run this command:
RESTORE FILELISTONLY
FROM MOVE 'logical_file_name_1' TO 'x:\full\path\to\data\file\new_db_name.mdf',

That will give you the list of logical file names you need to specify in the RESTORE command.  Decide on a new path and file name for all of those files.

Then complete and run this command:

RESTORE DATABASE new_db_name
FROM DISK = 'x:\full\path\to\backup\file\theirdb.bak'
WITH --REPLACE, --only if overlaying an already existing db (not recommended, put allowed)
MOVE 'logical_file_name_1' TO 'x:\full\path\to\data\file\new_db_name.mdf',
MOVE 'logical_file_name_2' TO 'x:\full\path\to\log\file\new_db_name_log.ldf'
--MOVE ...

Btw, no, do not try to do this from the GUI, you will only cause yourself more problems and time.
First restore it to a *local* server: you have to create a new, empty database on your server, then in SSMS: right-click the new database -> Tasks -> Restore -> Database -> change to "device" to select your .bak file. The trick is that you have to have a blank database, first. Getting it up to Amazon is a whole process, but here it is.
If you have an existing Microsoft SQL Server deployment that you want to move to Amazon RDS, the complexity of your task depends on the size of your database and the types of database objects that you are transferring. For example, a database that contains data sets on the order of gigabytes, along with stored procedures and triggers, is going to be more complicated than a simple database with only a few megabytes of test data and no triggers or stored procedures.

RDS for SQL Server service does not currently support RESTORE DATABASE ... FROM FILE, because the database and log file backups must be local to the SQL Server instance. Similarly, FILESTREAM is also not supported at this time.
If you do not have a local server you'll need to install one - just download SQL 2014 Express edition or buy a $50(ish) copy of the developer version.
Avatar of pzozulka
pzozulka

ASKER

Where do enter these commands. Do I need to download a tool? Note, that the SQL instance is running on Amazon's RDS.
SSMS, Sql Server Management Studio. It's easiest to install an Express version of SQL Server "with tools", which is SSMS:
https://msdn.microsoft.com/en-us/sqlserver2014express.aspx
Thanks all.

The .BAK file is only 5MB. Because RDS does not currently support restore from a file, it appears that neither of above solutions will work for me using SSMS?
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial