Looking for a stored procedure to restore from a .bak file, but would like to be able to enter the db name as a parameter, i.e. Exec sp_restore @dbname
- The paths to the .bak will be identical for all the dbs i.e. all dbs are dev copies of the same production backup.
- Each .mdf and .ldf will have the same name as the db itself i.e.
dbname = @dbname
,mdf = D:\data\@dbname.mdf
,ldf =D:\data\@dbname.ldf
- The paths to .mdf and .ldf will be the identical for each db, i.e.
D:\data\@dbname.mdf
Using Sql Server Management Studio, you can start performing a restore operation. Before completing the task, you should be able to see a "Script" button. This will create a script with all the parameters you have manually entered, including mdf and ldf location. You can then save this script and execute it at will. You can also modify the resulting script to make the database name an input variable. You can do anything really. It's SQL!
A sample script that restores a database and changes mdf and ldf file locations would look like this:
You can read more about the RESTORE statement
You can then insert the script in a stored procedure as such: