Move (Restore) SQL Server 2005 database which does not exist before

Question

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

• StanleyJohns: can you post the error you were getting.
• TheTechGuy: I added the error dialog, if I import it on top the existing db, then it works fine.

Answer 1

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.

• TheTechGuy: ARe dataFile and logFile required? I have to specify the output .mdf file as well?
• Thomas Stringer: @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.
• Aaron Bertrand: 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

Answer 2

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.

• TheTechGuy: I used SSMS database->taske -> restore option. Is it better to do it command line?
• Aaron Bertrand: Almost universally, yes.