Sql-server – Restore Database with Multiple Restore commands

sql serversql-server-2008sql-server-2008-r2t-sql

For automation, I have to dynamically construct the restore commands and thus I have created a script. A .bak file is created on a remote server and I receive it daily. The script builds the restore string and executes it.

Only hindrance so far is I'm unable to restore the db on multiple steps, because the back up file comes from an external source and fails with "Directory lookup for the file "xxxxxx.ndf" failed with the operating system error 3 (The system cannot find the path specified.)." It is the folder where the backup resides on the remote server.

Basically the script looks like:

RESTORE DATABASE x FROM DISK = 'E:x.bak'  WITH MOVE 'a' TO 'E:\x.mdf', NORECOVERY    
RESTORE DATABASE x FROM DISK = 'E:x.bak'  WITH MOVE 'b' TO 'E:\x1.ndf', NORECOVERY
RESTORE LOG x      FROM DISK = 'E:\x.bak' WITH MOVE 'c_log' TO 'E:\x.ldf',NORECOVERY
RESTORE DATABASE x WITH RECOVERY

How can I achieve such a scenario?

Best Answer

The following code will restore the database to the desired location:

RESTORE DATABASE x
FROM DISK = 'Filepathofbackupfile.bak'
WITH RECOVERY,
MOVE 'logicalDatafilename' TO 'Newfilepath.mdf',
MOVE 'logicalLogfilename' TO 'Newfilepath.ldf'

Related Solutions

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

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.

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.

Best Answer

Related Solutions

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

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

Related Question