Sql-server – Move (Restore) SQL Server 2005 database which does not exist before

backuppermissionsrestoresql serversql-server-2005

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

create a .bak file (backup the database)
Copy the .bak file to the remote server using shared folder
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

Best Answer

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.

Related Solutions

Sql-server – Restore a Database to a New database (T-SQL)

I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.

If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.

One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.

Sql-server – SQL Server 2008 Restore

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.

Best Answer

Related Solutions

Sql-server – Restore a Database to a New database (T-SQL)

Sql-server – SQL Server 2008 Restore

Related Question