0

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?

James Z
  • 12,209
  • 10
  • 24
  • 44
Arnab
  • 271
  • 2
  • 7
  • 18

2 Answers2

2

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

objectNotFound
  • 1,683
  • 2
  • 18
  • 25
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();
SqlOnly
  • 258
  • 1
  • 9