5

I did not ready about this but found out after trying to restore a database from remote server and it wont restore if the database did not exist before. What I would do is

  1. create a .bak file (backup the database)
  2. Copy the .bak file to the remote server using shared folder
  3. Run restore in SQL Server Management Studio to restore the database

It will give me error. I think I found it almost accidently (or out frustration, just restored it on top of the database, and it worked).

Now as I am typing the question, I realize move is probably not the same thing as restore. For restore, the database must already exist?

I am in the same position again to restore a database which does not exist on my system.

1. What procedure do I follow?

2. Do I need to set permission on the .bak file before restoring successfully on remote Server?

3. Or is there a better way?

Am I missing anything? I would like to know, what are the rules when restoring (or moving) the database.

Edit : Adding the dialog error. I just repeated the process. I am not sure this was the exact error I was getting before. I renamed the original db that I have. Then I import using SSMS.

enter image description here

Improve this question
2
  • can you post the error you were getting.
    – StanleyJohns
    Mar 13 '12 at 3:38
  • I added the error dialog, if I import it on top the existing db, then it works fine.
    – Jackofall
    Mar 13 '12 at 12:48
6

No, for a restore you don't have to have the database existing already (be mindful though to not have the with replace option set in your restore database command).

The below statement would create a new database from the backup:

RESTORE DATABASE [YourNewDbName] 
FROM  DISK = N'C:\YourBackupFile.bak' 
WITH  FILE = 1,  
    MOVE N'YourDataFile' TO N'C:\YourNewDbName.mdf',  
    MOVE N'YourLogFile' TO N'C:\YourNewDbName_1.ldf',  
    NOUNLOAD,  
    STATS = 10
GO

Obviously, modify the parameters accordingly. But this doesn't require YourNewDbName to exist prior to the restore database.

Improve this answer
3
  • ARe dataFile and logFile required? I have to specify the output .mdf file as well?
    – Jackofall
    Mar 12 '12 at 16:58
  • 1
    @Jackofall You don't have to, but if you have any contention for the file location of the backup metadata then you will run into an error. For instance, if you data files existed on some directory, and that directory doesn't exist on your server that you are restoring it on, or if there is already an existing file with that filename (in that path) then you will also get an error.
    – Thomas Stringer
    Mar 12 '12 at 17:04
  • If you only have an MDF file and not a BAK file, then you can use CREATE DATABASE ... FOR ATTACH or ... FOR ATTACH_REBUILD_LOG - see Example E for a starting point (just pretend they don't use dynamic SQL): msdn.microsoft.com/en-us/library/ms176061%28SQL.90%29.aspx
    – Aaron Bertrand
    Mar 12 '12 at 18:06
3

No, a database does not need to exist already in order to restore a backup. The error you received was probably because you copied some restore command from somewhere and it had options that prevented it from working successfully. It will be easier to help you sort out the issue if you post the actual RESTORE command you ran, and the actual error message you received.

Improve this answer
2
  • I used SSMS database->taske -> restore option. Is it better to do it command line?
    – Jackofall
    Mar 12 '12 at 17:00
  • 1
    Almost universally, yes.
    – Aaron Bertrand
    Mar 12 '12 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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