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 usingWITH MOVE
.