0

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?

  • 1
    Can you put the command that you used for this? – Krismorte Jan 10 at 2:12
  • I also recommend that you put a few screenshots here so people can understand your issue better. – jyao Jan 10 at 5:53
  • The question is not clear. What does he mean by main database? – Priyanka Jan 10 at 6:50
  • Question is clear. Can you create a blank DB fine? Is it only "restoring from BAK file, to a new/existing DB" causing issue? – Jerry Hung Jan 10 at 7:35
  • by main database you mean Master? – Racer SQL Jan 10 at 17:51
2

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.

0

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.enter image description here

0

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:

  1. Make sure you only have the original database only. Don't create a new empty database.
  2. Right click in SSMS on "Databases", and chose "Restore database.."
  3. 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.
  4. Select "files" in the menu to the left, and modify where you want your files to be stored for the new database.
0

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'

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.