Mini Post: Backing Up/Restoring From The Command Line

Hey everyone and welcome back to SQL Something!

I happened to see an old post of mine (towards the bottom of the post) where I mentioned restoring from the command line but I did not actually say how.

So that's what I want to look at really quickly.

 

Backing up from the Command Line

To create a backup from the cmd line, we'll first need to know how to create a backup via a query. The simplest example is as follows:

BACKUP DATABASE YourDatabaseName TO
DISK = ’YourFilePath\YourDatabaseBackupName.bak'

Cool. Now open a cmd prompt and enter the following:

SqlCmd -E -S YourServerAndInstanceName –Q “BACKUP DATABASE YourDatabaseName TO DISK = ’YourFilePath\YourDatabaseBackupName.bak’”

Let's look at the pieces:

• SqlCmd: lets command prompt know we are going to enter TSQL commands. 
• -E: Use a trusted connection.
• -S: Indicates you're going to enter your server\instance name.
• -Q: The important part, indicates you're going to enter a your TSQL command.

You can see a full list of the SqlCmd commands here.

 

Restoring from the Command Line

Like backing up, we'll need to know the initial TSQL command. In it's simplest form:

RESTORE DATABASE YourDatabaseName
FROM DISK = 'YourFilePath\YourDatabaseBackupName.bak'

A bit more complicated version:

RESTORE DATABASE YourDatabaseName
FROM DISK = 'YourFilePath\YourDatabaseBackupName.bak'
WITH MOVE 'YourLogicalMDFName' TO 'YourNewFilePath\YourNewLogicalMDFName.mdf',
MOVE 'YourLogicalLDFName' TO 'YourNewFilePath\YourNewLogicalLDFName_Log.ldf'

The second version allows you to change the name and location of the restored DB's MDF and LDF files.

If you are using the second version, in order to get the logical filenames from the backup, run this in the cmd prompt first:

SqlCmd -E -S YourServerAndInstanceName –Q "RESTORE FILELISTONLY FROM DISK = 'YourFilePath\YourDatabaseBackupName.bak'"

This will display the logical filenames.

Now enter the below in the cmd prompt:

SqlCmd -E -S YourServerAndInstanceName –Q "RESTORE DATABASE YourDatabaseName FROM DISK = 'YourFilePath\YourDatabaseBackupName.bak' WITH MOVE 'YourLogicalMDFName' TO 'YourNewFilePath\YourNewLogicalMDFName.mdf', MOVE 'YourLogicalLDFName' TO 'YourNewFilePath\YourNewLogicalLDFName_Log.ldf'"

And that's all there is to it. :-)

Again, You can see a full list of the SqlCmd commands here.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)