Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

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.

share|improve this answer
    
Hmmm...I get it now why I see so many references to using MOVE. Then when I create the database I should also create all the required data files and then use the MOVE to specify that location? I'm editing my question to add a basic script of the db creation, just to use it as a sample. –  Yaroslav Feb 18 at 14:58
    
@Yaroslav I still don't know why you think you need to create an empty database first - what do you think that does to help the process? Just run your RESTORE ... WITH MOVE (and WITH REPLACE in case the database already exists). –  Aaron Bertrand Feb 18 at 15:01
    
Edited my question as you where answering. The main issue here is that we already have an script (scheduled and working) with all the restore process that I don't want to modify (at least until I feel confident I can do it without breaking anything) and just want to add a create database statement in case the database being restored doesn't exist. So I don't modify the rest of the already working script. –  Yaroslav Feb 18 at 15:05
    
@Yaroslav I'm still confused about what is going to break. If your script that is scheduled and working is just running a RESTORE (with the change to add WITH MOVE of course), why do you think it will break if you don't create the destination database first? RESTORE does not need the database to already exist. –  Aaron Bertrand Feb 18 at 15:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.