1

In SQL server user have to follow below steps to restore database from backup file.

  1. right click on the Databases container within object explorer.

  2. from context menu select Restore database.

  3. Specify To Database as either a new or existing database.

  4. Specify Source for restore as from device.

  5. Select Backup media as File.

  6. Click the Add button and browse to the location of the BAK file.

Is there any script/command to restore the same without doing the above methods?

4
  • you also can use restore command instead of GUI Commented Jul 26, 2016 at 18:49
  • Can you please help me with an example? Commented Jul 26, 2016 at 18:51
  • mssqltips.com/sqlservertip/2842/… Commented Jul 26, 2016 at 18:52
  • Fully automated SQL Server Restore, have a look: stackoverflow.com/questions/2510295/… Commented Jul 26, 2016 at 20:11

2 Answers 2

3

Just before you click on the last OK button on SSMS Backup/Restore Wizard ... you can Click on the script drop down button and pick script to New Query Window (The script drop down is Next to the Help button and above the Source/Database Name) and it will generate the Exact command that SSMS will execute against the DB. So you can use that command from the Query window to do the same thing programatically in TSQL. Indeed you can even put that inside a Stored PRocedure if you want to execute the same command all the time like so:

CREATE PROCEDURE dbo.dbBackup AS
BEGIN

   RESTORE DATABASE [MyDataBaseName] FROM  DISK = N'C:\SQLData\MyDB.bak' 
   WITH  FILE = 1,  NOUNLOAD,  STATS = 5

END

And to take it one more level you could assign that SP to a hotkey as explained over here : https://www.mssqltips.com/sqlservertip/1287/assign-shortcuts-to-commands-in-sql-server-management-studio/

To access the screen below, open a query window and select from the menu Tools -> Options and then under the Environment node select Keyboard --> Query Shortcuts this will allow you to assign a stored procedure to execute for the Hotkey combination that is best for you.

One thing to be care full about though is to make sure that this does not replace an existing DB because you could accidentally overwrite a good DB if the restore command includes that option

Sign up to request clarification or add additional context in comments.

Comments

1

"Automate" is a very broad term in your question, but if C# is an option, you can use the SQLCommand Class to execute the Restore, as below

sqlCmd.CommandText = "RESTORE DATABASE [" + restoreDbName + "] FROM DISK = 
    N'" + backupFileName + "' WITH FILE = 1, MOVE '" + logicalNameMdf + "' TO '" 
    + restorePathMdf + "', MOVE '" + logicalNameLdf + "' TO '" + restorePathLdf + "', " 
    +  " NOUNLOAD,  REPLACE";

sqlCmd.ExecuteNonQuery();

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.