That a database restore fails, can be due to several reasons. I actually didn’t figure out why it didn’t work for me but I found a workaround. Since it is a very similar process I will also show you how you can copy a database on the same server with a different name.
Contents
Idea 1: Take the database offline
My first idea was to take the database offline because I thought that there might be open connections. This solution did not work but I would like to mention it since you might also think about trying this 😉 The problem that followed was that I closed Management SQL Studio and wanted to connect later to do the restoring. The database I had taken offline was my default database (I did not know there was something like that) and I could not connect to my database server anymore.
Luckily for that problem there is a great workaround. By changing the database to connect to in the login window, you can still connect to your database. After that I took the database online again and looked for a different solution. Would have been nice of the Management Studio to tell me that I am taking down my default database 😀
Idea 2: Delete database and restore it
Around the same time I needed to make a copy of a database on the same server as the database. When looking for a solution I found this stackoverflow answer. It gave me the idea that I could simply delete the database I wanted to restore and then restore it (then there are definitely no connections or anything running that stops the database from being restored).
Right click the database > delete. Uncheck the box that asks about deleting backups!
Now you are ready to go and restore your database: Rightclick Databases > Restore databases … Select the backup (.bak file) you want to restore your database to.
In case you want to copy the database you additionally have to change the name of the database to something new.
The next steps needs only be done if you want to copy the database to the same server with a different name. Since each database has a [name].mdf and a [name]_log.ldf file that are stored in the same directory you have to change the name, otherwise you will get the error that this files already exist. For this you have to click on “Files” and change the name where it says myDatabase.mdf and myDatabase_log.ldf to something different although I would suggest you use the same name here that you used above to name your database. And I would also keep the suffix “_log”.
Then click “OK” and that is it 🙂 If you get into any problems feel free to leave me a comment!
