Quite a simple blog here, one of those if I don’t write it down I’ll forget. There seem to be an awful lot of examples on the internet in how to perform a SQL Server restore, all of them using an awful lot of words. I just wanted to condense a common example down, that example being synchronising a Live Environments database into the Test Environment. We have several clients who do this everyday and we have to implement this feature for a new client and so I thought I’d just simply document the conceptual scriptlets and architecture used. The scheduling is performed via SQL Server Agent and I’m not really going to into any more detail than that other than to say there are in this simplistic example 2 steps, a back up scriptlet and a restore scriptlet.
I have, in this instance a BogusData database which is my ‘live’ database and a ‘test’ database entitled SSISDB_BU
Lets start with the BackUp Database script, we wish to create a .bak file on the disk and so here is that entire back up database script.
BACKUP DATABASE BogusData
TO DISK = ‘C:\Backup\BogusData.bak’
WITH FORMAT
Nice and simple, if you are to run this in Management studio you should see the bak file created. By default this method will overwrite any other backs within that file so you don’t need to worry about an ever expanding file. We can now look into the Restore side of things. Now things are a little more complicated here. Every database has what are called logical filenames, for obvious reasons these can be named anything you define. There may well, in fact, be many more than the minimal two logical files that I am going to cover but the same logic applies in those instances too, you will just have more move statements. The first thing you will need to know is what are the names of these logical files, so if we run the following script in SQL Server Management Studio (despite its name it does not actually restore anything, it just reads a restore file) we should see the list of logical files within our back up file:-
RESTORE FILELISTONLY FROM DISK = ‘C:\Backup\BogusData.bak’
Upon running this you will see something like the following:-
Using your logical names (and the type field (D= Data, L=Log if required) you should then be able to compile your Restore command. Lets start by looking at the main Restore command, Breaking this statement down bit by bit you can see that firstly we are restoring into our test ‘SSISDB_BU’ database from the back up file that we created earlier:-
RESTORE DATABASE SSISDB_BU
FROM DISK = ‘C:\Backup\BogusData.bak’
the WITH REPLACE directive instructs SQL server to overwrite the current SSISDB_BU database with whatever is in this back up.
WITH REPLACE,
We are then left with two move statements.
MOVE ‘BogusData’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SSISDB_BU.mdf’,
MOVE ‘BogusData_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SSISDB_BU_log.ldf’
The first of these instructs SQL Server to store (MOVE) the data within the BogusData logical file to the physical data file entitled SSISDB_BU.mdf whilst the second instructs SQL Server to store (MOVE) the data stored within the logical BogusData_log file in the log file entitled SSISDB_BU_LOG.ldf.
In all that gives us the following RestoreDatabase scriptlet that forms the second of our agent job steps
RESTORE DATABASE SSISDB_BU
FROM DISK = ‘C:\Backup\BogusData.bak’
WITH REPLACE,
MOVE ‘BogusData’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SSISDB_BU.mdf’,
MOVE ‘BogusData_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SSISDB_BU_log.ldf’
Go forth, back up and restore the world…