I'm a newbie when it comes to SQL Server, it would be helpful if someone could point me in the right direction.
We have a database file that is called main-db.bak
.
This is the database file that we have cloned as it contains all the main things we need in the process of constructing new websites for our clients. Is it possible to do the following:
- Have a copy of the database on the same SQL Server Instance
- Create a new empty database
- Restore the database from
main-db.bak
file and not overrite anything else that may be using the same data MDF and LDF files?
Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?
Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?
Best Answer
Lets take your questions one by one :
e.g. Provided you dont have secondary data files, below will work
-- find logical names
No need to first create an empty database, as the
restore with move
will create it.Same as above. As
restore with move
will not over write unless you userestore with REPLACE
explicitly to overwrite.Cannot understand what you mean by this ! If you want to create a new database or copy of the current database , you have to take a backup and then use
restore database
to restore it.If you want to overwrite the existing one, then no. If not and you want to create a copy of existing one, then Yes - you have to rename the mdf and ldf files.
You can fully automate the restore process. Some examples can be found here and here.