I have a peculiar issue on my hands. I have a few databases in my SSMS already (2014), and when creating a new database and performing a restore from a file that has never been used, it sets my main database to single-user mode. During the restore I initially didn't realize it was moving the main DB to the new location. I don't want this, but regardless of what I do it doesn't allow me to just restore this to the selected database. I have been through the google search gauntlet, finding no results matching what I am experiencing. I have also attempted to just create a new database by setting restore as my option, but the main database is selected. Does anyone have a sure-fire way to get this issue resolved so I can add this other desired database to my system?
The RESTORE DATABASE command will create the database for you.
Don't create it first because then it will have to be deleted by the RESTORE command and a type if safety check in there might not allow the restore unless you specify the REPLACE option.
Also, is the database in that backup was read only when the backup was produced, then it will be read only when restore. Just turn off that database option after you done the restore.
Here are the screenshots so that you can see the process. I have attempted to create a new database by just going to Databases > Right-click > Restore and the same options in the screenshot are provided. I have reviewed the process online but am unable to find anything that will work and not affect my existing database AccessControl.
If you have a backup file of a database(possibly called .bak), and you have the original database currently running, and you want to restore the backup to create a copy of the original, so that you have 2 similar databases:
- Make sure you only have the original database only. Don't create a new empty database.
- Right click in SSMS on "Databases", and chose "Restore database.."
- Select "Device" instead of "Database" as source. There should be a grey empty input-field. To the right of it, Select the "..."-button, and select your backup-file.
- Select "files" in the menu to the left, and modify where you want your files to be stored for the new database.
Your better option is just scripting out the options that you want in the
RESTORE statement. The following command will create a new database from a backup of another database (.BAK file) by the name specified and the database will be available in
MULTI_USER mode upon completion of the restore. Of course, make sure the name of the new database is different from the names of any other databases on the instance and this will not impact any existing databases. The logical names for the primary and log files will need to be the same as they were for the database that the backup was made from. You will want to give the physical files a new name, using the name of the database that will be created as a result of the restore operation. This example assumes no log files are used. If there are the
RECOVERY option for each file will need to be changed to
NORECOVERY beginning with the initial .BAK file and any subsequent log file
RESTORE commands, until the last log restore which will use the
RECOVERY option to complete the restore.
USE [master] RESTORE DATABASE [NewDatabaseName] FROM DISK = N'C:\YourFilePath\YourBackupFile.bak' WITH RECOVERY, REPLACE, MOVE N'DatabaseLogicalPrimaryFileName' TO N'C:\Folder\DATA\NewDatabaseName.mdf', MOVE N'DatabaseLogicalLogFileName' TO N'C:\Folder\DATA\NewDatabaseNameLog.ldf'