Restore a SQL Server Backup (WITH MOVE)

This is a basic skill can get overlooked, so let’s talk about a common DBA task: backing a database up and restoring it to another server.

First let’s dispel a couple of common misconceptions:

  • You cannot restore a backup from a later version of SQL Server, to an earlier version. For example, a 2016 backup will not restore on to a 2012 instance!
  • You cannot restore single objects from a native SQL Server backup. There are some third party tools that can do that, I believe, but SQL Server itself CANNOT restore a single table or SP.

So far, so good.

Now when we want to restore a backup, it’s very likely that the directory and/or file structure on the target server isn’t the same as the source. For example, maybe when we backed up DB1 on Server1, the database files were on D:\SQLServer\.

But we’re restoring to Server2, which doesn’t even have a D: drive. In fact, the data file(s) is supposed to go on M:\SQLData\, and the log should go on L:\SQLLog\.

Accomplishing this is a simple, two step process:

  1. Get the logical file names and physical file names that you’ll be restoring. You can either look on the source server (using exec sp_helpdb ‘DBNAME’) or you can do FILELISTONLY.
  2. Write your restore statement WITH MOVE.

Let’s talk about FILELISTONLY and WITH MOVE.

RESTORE FILELISTONLY

“FILELISTONLY” is a restore option that lets you see the logical and physical file names contained in the  backup. This is important to know, because you won’t always have access to the source server.

To use FILELISTONLY, simply run:

RESTORE FILELISTONLY 
FROM DISK = '\\location\folder\backupname.bak";

This will return the logical file name, physical file name, file type, and a bunch more information.

Let’s say that our results are:

LogicalName PhysicalName
DB1 D:\SQLServer\DB1.mdf
DB1_log D:\SQLServer\DB1_log.ldf

Now that we have that info, we can write the RESTORE…WITH MOVE statement.

RESTORE WITH MOVE

Remember, we said we needed to restore DB1 so that the data file is in M:\SQLData\, and the log file is in L:\SQLLog\. Here is our restore statement:

RESTORE DATABASE [DB1]
FROM DISK = '\\location\folder\backupname.bak"
WITH MOVE 'DB1' TO 'M:\SQLData\DB1.mdf',
MOVE 'DB1_Log' TO 'L:\SQLLog\DB1_log.ldf';

Once you get used to the idea, RESTORE WITH MOVE makes perfect sense!

CROSS JOIN for fun

My son, who wishes to be known as “Pigeon” for the duration of this blog, was mixing the names of Hogwarts houses over a game of chess* with his brother:

ONE MILLION POINTS TO GRYFFINPUFF!”

So I said, “Hey guys, I’m going to do a nerdy thing with my database skillz and then show you, okay?

Here’s what I did:

CREATE TABLE #names (
 firstpart varchar(20), 
 lastpart varchar(20)
)
INSERT INTO #names (firstpart, lastpart)
VALUES ('Raven', 'Claw'),
('Slyther', 'In'),
('Huffle', 'Puff'),
('GrYffin', 'Dor')
SELECT a.firstpart + b.lastpart
FROM #names a
CROSS JOIN #names b
ORDER BY 1;

And what we get, of course is a complete list of all the possible Hogwarts houses names, if they were mixed and matched:

  • GryffinClaw
  • GryffinDor
  • GryffinIn
  • GryffinPuff
  • HuffleClaw
  • HuffleDor
  • HuffleIn
  • HufflePuff
  • RavenClaw
  • RavenDor
  • RavenIn
  • RavenPuff
  • SlytherClaw
  • SlytherDor
  • SlytherIn
  • SlytherPuff

See there? Who said CROSS JOIN isn’t useful?

-Jen

*So, you know, I’m not the only nerd here.

Where on Earth is the SQL Server Reporting Services Config Manager?

While we’re on the topic of SQL Server Reporting Services: WHYYYYYY is the Reporting Service Configuration Manager so hard to find from one server to the next?

For one thing, it doesn’t help that I’m used to hitting the Windows key and typing “SQL Server” for a list of, oh, say, SQL SERVER RELATED PROGRAMS AND OPTIONS

SQL Server options

But noooo, it’s just the Reporting Services Configuration Manager.

sqlserver2

For another thing – and I have not yet figured out why this is – on some servers, even hitting windows and typing in “Reporting Services” does not cause the SSRS config manager to show up. Even navigating to the Windows menu > SQL Server sometimes does not reveal the SSRS config manager icon, even on servers where SSRS is installed and running.

It’s a mystery.

In any case, for your (and my) convenience, here is the very Googleable “how do I find the SQL Server Reporting Services Configuration Manager”* answer of your (my) dreams:

Run the SSRS config manager tool  by navigating to  

c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\RsConfigTool.exe

…and then pin that sucker to your start menu!

Happy days,
Jen

 

*Or,

  • “how do I find the SSRS Configuration Manager”
  • “how do I find the SQL Server Reporting Services Config Manager”
  • “how do I find the SSRS Config Manager”
  • “how do I find the SQL Reporting Configuration Manager”
  • “how do I find the SQL Reporting Config Manager”
  • “where the hell is the damn SSRS config mgr”

Real news, real tech.