SQL Server Restore – Create Database Before Restoring Multiple Files

backuprestoresql-server-2008-r2

I've several database, most of them have multiple files (both data and logs) and multiple diff and trn backups. I've already a restore script (scheduled and working on other server) that takes all the needed files and restores them through UNC path. There is a check at the beginning that verifies that the db exists, if not, throws an error. But I want instead to create the database and then restore it. I don't want to use the MOVE option on the RESTORE statement. I've made some searches and what I've found doesn't cover my needs.

So, how can I create a CREATE statement to match what I will restore on the next step? Maybe a silly question, but can't find how to do it.

Edit

From Aaron comment I get now why I can't prevent using MOVE. But then I should create the database with all the requiered filegroups, correct? Something like this:

CREATE DATABASE [Testdb] ON  PRIMARY 
    ( NAME = N'Testdb', FILENAME = N'F:\SQLDBs\Testdb.mdf' , SIZE = 102400KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
 FILEGROUP [TEST_DATA] 
    ( NAME = N'Test_Data', FILENAME = N'F:\SQLDBs\Test_Data.ndf' , SIZE = 5242880KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
 FILEGROUP [TEST_DATA2] 
    ( NAME = N'Test_Data2', FILENAME = N'F:\SQLDBs\Test_Data2.ndf' , SIZE = 5242880KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
 FILEGROUP [TEST_INDEX] 
    ( NAME = N'Test_Index', FILENAME = N'F:\SQLDBs\Test_Index.ndf' , SIZE = 5242880KB,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON 
    ( NAME = N'Testdb_log', FILENAME = N'G:\SQLLogs\Testdb_log.ldf' , SIZE = 1048576KB,
      MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [Testdb] SET COMPATIBILITY_LEVEL = 100
GO

Best Answer

I think the main problem is that you think that creating the database first will allow you to bypass WITH MOVE. This is not the case.

If the destination file paths don't match those contained in the backup file, the presence of a database (and its file locations) aren't going to make a difference. RESTORE is going to look at the file locations specified in the backup file, completely ignoring the existing database. And this can only be overridden by using WITH MOVE.