x

restore from just the *.BAK files

-- presently using this script
-- attempting to automate as much as possible
-- backup files located on G:\Transfer\ *.BAK
-- Having problems with dynamic execution EXEC @Restore
-- this is the error
RETURN yitzstokes@gmail.com

/*
RESTORE DATABASE [CSOS]
FROM DISK =
    'G:\Transfer\CSOS.BAK'
WITH
    MOVE 'CSOS_Data' TO 'E:\Data\CSOS_Data.MDF' ,' is not a valid identifier.
*/

USE [master]
GO

SET NOCOUNT ON
GO

DECLARE
  @backup_path varchar(500),
  @totCount int,
  @Current int,
  @dirCmd varchar(200),
  @Filename varchar(200),
  @DatabaseName varchar(200),
  @CreateDBFolder bit,
  @tab varchar(1),
  @cr varchar(2),
  @new_data_path varchar(500),
  @new_data_path2 varchar(500),
  @new_log_path varchar(500),
  @new_log_path2 varchar(500),
  @Restore nvarchar(4000)

SELECT @backup_path = 'G:\Transfer\' --> Folder where full database backupfiles are located
SELECT @new_data_path = 'E:\Data\' --> Optional, leave blank to use the default from the backup file
SELECT @new_log_path = 'F:\Log\' --> Optional, leave blank to use the default from the backup file
SELECT @CreateDBFolder = 0   --> If 1 is specidied the data/log files will be placed in a separate folder named after the database

CREATE TABLE #BackupFiles (
  ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  [Filename] VARCHAR(500)
)

SET @dirCmd = 'dir /b "' + @backup_path + '*.bak" '

INSERT INTO #BackupFiles ([Filename])
EXEC xp_cmdshell @dirCmd

--select * from #BackupFiles

--drop table #BackupFiles


print ' Delete excess row in #BackupFiles'
DELETE #BackupFiles WHERE [Filename] IS NULL OR [Filename] NOT LIKE '%.bak'

print ' Delete excess row in #BackupFiles completed'

CREATE TABLE #header (
  BackupName nvarchar(128),
  BackupDescription nvarchar(255),
  BackupType smallint,
  ExpirationDate datetime,
  Compressed bit,
  Position smallint,
  DeviceType tinyint,
  UserName nvarchar(128),
  ServerName nvarchar(128),
  DatabaseName nvarchar(128),
  DatabaseVersion int,
  DatabaseCreationDate datetime,
  BackupSize numeric(20,0),
  FirstLSN numeric(25,0),
  LastLSN numeric(25,0),
  CheckpointLSN numeric(25,0),
  DatabaseBackupLSN numeric(25,0),
  BackupStartDate datetime,
  BackupFinishDate datetime,
  SortOrder smallint,
  CodePage smallint,
  UnicodeLocaleId int,
  UnicodeComparisonStyle int,
  CompatibilityLevel tinyint,
  SoftwareVendorId int,
  SoftwareVersionMajor int,
  SoftwareVersionMinor int,
  SoftwareVersionBuild int,
  MachineName nvarchar(128),
  Flags  int,
  BindingID uniqueidentifier,
  RecoveryForkID uniqueidentifier,
  Collation nvarchar(128),
  FamilyGUID uniqueidentifier,
  HasBulkLoggedData bit,
  IsSnapshot bit,
  IsReadOnly bit,
  IsSingleUser bit,
  HasBackupChecksums bit,
  IsDamaged bit,
  BeginsLogChain bit,
  HasIncompleteMetaData bit,
  IsForceOffline bit,
  IsCopyOnly bit,
  FirstRecoveryForkID uniqueidentifier,
  ForkPointLSN numeric(25,0) NULL,
  RecoveryModel nvarchar(60),
  DifferentialBaseLSN numeric(25,0) NULL,
  DifferentialBaseGUID uniqueidentifier,
  BackupTypeDescription nvarchar(60),
  BackupSetGUID uniqueidentifier NULL,
  CompressedBackupSize bigint,
  Seq int NOT NULL identity(1,1)
  )

CREATE TABLE #filelist (
  LogicalName nvarchar(256),
  PhysicalName nvarchar(260),
  Type char(1),
  FileGroupName nvarchar(256),
  Size numeric(20,0),
  MaxSize numeric(20,0),
  FileID bigint,
  CreateLSN numeric(20,0),
  DropLSN numeric(20,0) NULL,
  UniqueID uniqueidentifier,
  ReadOnlyLSN numeric(20,0) NULL,
  ReadWriteLSN numeric(20,0) NULL,
  BackupSizeInBytes bigint,
  SourceBlockSize int,
  FileGroupID int,
  LogGroupGUID uniqueidentifier NULL,
  DifferentialBaseLSN numeric(20,0) NULL,
  DifferentialBaseGUID uniqueidentifier,
  IsReadOnly bit,
  IsPresent bit,
  TDEThumbprint varbinary(32),
  Seq int NOT NULL identity(1,1)
  )

print ' tables header & filelist created'


SET @totCount = (SELECT COUNT(*) FROM #BackupFiles)
SET @Current = 1

SELECT @tab = char(9), @cr = char(13)+Char(10)

--> Loop through the files that hasn't been restored before and restore them one by one
print 'starting loop ' + 'total count:'+cast(@totCount as char(5))

WHILE (@Current <= @totCount AND @totCount > 0)
  BEGIN
    SELECT @Filename = [Filename] FROM #BackupFiles WHERE ID = @Current
    INSERT INTO #header
    EXEC ('RESTORE HeaderOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    INSERT INTO #filelist
    EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @backup_path + @Filename + '''')

    SELECT 
      @DatabaseName = Databasename,
      @new_data_path2 = CASE 
        WHEN @CreateDBFolder = 1 THEN @new_data_path + DatabaseName + '\' 
        ELSE @new_data_path
        END,
      @new_log_path2 = CASE 
        WHEN @CreateDBFolder = 1 THEN @new_log_path + DatabaseName + '\' 
        ELSE @new_log_path
        END
    FROM #header
    --SET @Current = @Current + 1

    print 'Replace the old data file path with a new one if specified'
    IF @new_data_path2 <> ''
      UPDATE #filelist
      SET PhysicalName = @new_data_path2 + right(physicalname,
      CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'D'

    print' Replace the old log file path with a new one if specified'
    IF @new_log_path2 <> ''
      UPDATE #filelist
      SET PhysicalName = @new_log_path2 + right(physicalname,
      CHARINDEX('\',REVERSE(physicalname))-1)
      WHERE Type = 'L'

    --> Print restore script
    declare @restore_script table (vrestore nvarchar(4000))
    insert into @restore_script
      select   CASE 
          WHEN a.Seq = 1 AND @CreateDBFolder = 1 THEN 
            @cr + 'EXEC xp_cmdshell ''md ' + @new_data_path2 + '''' +
            @cr + 'EXEC xp_cmdshell ''md ' + @new_log_path2 + ''''
          ELSE ''
        END
        +
        CASE
          WHEN a.Seq = 1 THEN @cr +
            @cr + 'RESTORE DATABASE [' + c.DatabaseName + ']' +
            @cr + 'FROM DISK =' + @cr + @tab + '''' +
            @backup_path + @Filename + '''' + @cr + 'WITH' + @cr
          ELSE ''
        END
        +
        @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''' ,'
        +
        CASE 
          WHEN a.Seq = b.Seq THEN
            @cr + @tab + 'REPLACE'
          ELSE ''
        END    

    FROM
      #filelist a
      CROSS JOIN
        (SELECT Seq = MAX(b1.Seq) FROM #filelist b1 ) b
      CROSS JOIN
        (SELECT DatabaseName = MAX(c1.DatabaseName) FROM #header c1) c
    ORDER BY
      a.Seq

    SET @Current = @Current + 1

    --@Restore
    -- exec (@Restore)
    TRUNCATE TABLE #header
    TRUNCATE TABLE #filelist
END
print 'execute restore script table'

--select * from @restore_script;


print 'execute loop to execute string'

declare curs cursor for
select vrestore from @restore_script;

open curs;
fetch next from curs into @Restore; 
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
 --  print @Restore;
   EXEC @Restore;  
 -- print 'exec '+substring(@restore,17,10)
   fetch next from curs into @Restore ;
END;
PRINT 'databases restored';
CLOSE curs;
DEALLOCATE curs;
GO



--select * from #header
--select * from #filelist


DROP TABLE #header
DROP TABLE #filelist
DROP TABLE #BackupFiles
more ▼

asked Jun 10, 2011 at 10:15 AM in Default

yitzstokes1 gravatar image

yitzstokes1
11 1 1 1

As you have requested

dbrestore

RESTORE DATABASE [CSOS]
FROM DISK =
    'G:\Transfer\CSOS.BAK'
WITH
    MOVE 'CSOS_Data' TO 'E:\Data\CSOS_Data.MDF' ,
    MOVE 'CSOS_Log' TO 'F:\Log\CSOS_Log.LDF' ,
    REPLACE


RESTORE DATABASE [GMWorkload]
FROM DISK =
    'G:\Transfer\GMWorkload.BAK'
WITH
    MOVE 'GMWorkload_Data' TO 'E:\Data\GMWorkload_Data.MDF' ,
    MOVE 'GMWorkload_Log' TO 'F:\Log\GMWorkload_Log.LDF' ,
    REPLACE


RESTORE DATABASE [LCEWEB]
FROM DISK =
    'G:\Transfer\LCEWEB.BAK'
WITH
    MOVE 'LCEWeb_Data' TO 'E:\Data\LCEWeb_Data.MDF' ,
    MOVE 'LCEWeb_Log' TO 'F:\Log\LCEWeb_Log.LDF' ,
    REPLACE
Msg 203, Level 16, State 2, Line 231 The name 'RESTORE DATABASE [CSOS] FROM DISK = 'G:\Transfer\CSOS.BAK' WITH MOVE 'CSOS_Data' TO 'E:\Data\CSOS_Data.MDF'
> ,' is not a valid identifier.
Jun 13, 2011 at 04:42 AM yitzstokes1
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You have problem with below part of your statement:

  +
    @tab + 'MOVE ''' + a.LogicalName + ''' TO ''' + a.PhysicalName + ''' ,'
    +
    CASE 
      WHEN a.Seq = b.Seq THEN
        @cr + @tab + 'REPLACE'
      ELSE ''
    END

You are always adding the comma to the command even there is no REPLACE part. So if you do not include the REPLACE part, then your statement ends with comma, what is source of your error.

Here you have the problem: a.PhysicalName**+ ''' ,'**.

You need to put the coma as part of the REPLACE part..

==EDIT==
I took a deeper look on your code and found that the real problem is not exactly the code I mentioned above, but the way how you are executing the final Script. But it's caused by the comma as I mentioned above.

When you put your scrip into the @restore_script table variable, then in first row you have a RESTORE DB statement plus MOVE of the first database file. Then in subsequent rows you have the other files + LOG.

You are then iterating through the lines of @restore_script

declare curs cursor for
select vrestore from @restore_script;

open curs;
fetch next from curs into @Restore; 
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
 --  print @Restore;
   EXEC @Restore;  
 -- print 'exec '+substring(@restore,17,10)
   fetch next from curs into @Restore ;
END;
PRINT 'databases restored';
CLOSE curs;
DEALLOCATE curs;

You will execute the RESTORE COMMAND with MOVE for the first line and it ends with comma on end. This will fail. In subsequent iterations of the loop you will read the MOVE of other files + log, without any restore command.

You need to combine all the lines into a single statement and execute the statement at once. Eg. combine the rows into the @Restore variable:

SELECT @Restore = ''
SELECT
    @Restore = @Restore + @cr + vrestore
FROM @restore_script

EXEC (@Restore)
==END EDIT==
more ▼

answered Jun 10, 2011 at 01:01 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Also instead inserting the records into the @restore_script table variable, you could directly construct the statement in the @Restore variable.

So instead of

INSERT INTO @restore_script 
SELECT
...
...

you could use

SELECT
@Restore = @Restore + @cr +
...
...
...
Jun 10, 2011 at 02:02 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

You say "This is the error" but there is no error there - only TSQL.

If the drives are not local to the server where this script is being executed then you will need to reference them in the form \\\servername\sharename\directory\filename.bak

If you can post the error then we may be able to suggest a more accurate solution.

[Edit after error message posted]
I think this is possibly because you are using a drive letters to reference shared directories. try using the servername as above. eg if you have drive G: mapped to a share on BigServer then instead of using G:\\SharedDirectory use \\BigServer\Sharename
more ▼

answered Jun 10, 2011 at 12:42 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

the error is at line EXEC @Restore; when I use print @Restore; I receive

restore database db_name
from disk = 'G:\transfer\dbname.bak'
with move db_data to 'e:\data\dbname_data',
move db_log to 'f:\log\dbname_log',replace

EXEC @Restore;
restore database db_name
from disk = 'G:\transfer\dbname.bak'
with move db_data to 'e:\data\dbname_data',  <---- unspecified specification... 
--this is the message
Jun 10, 2011 at 12:53 PM yitzstokes1
error message provided
Jun 13, 2011 at 04:42 AM yitzstokes1
(comments are locked)
10|1200 characters needed characters left

tried something a little different
being that I am running everything from the command line to perform a restore
thank all for their suggestions

1- made a physical table restore_script
2- performed EXEC xp_cmdshell 'bcp "select * from restore_script" queryout G:\automation\jasrestore.txt -T -c'
3- referenced :r G:\automation\jasrestore.txt

restore completed

-- this is the master script that calls them all  
SET DbServer=myservername  
SET DestinationDatabase=Master    
SET SourceDatabase=Master  


SET RUNSQL=sqlcmd -S %DbServer% -d %DestinationDatabase%  
%RUNSQL% -v srcdb=%SourceDatabase% destdb=%DestinationDatabase% -i"G:\automation\createall.sql"

-- contents createall.sql  -- are commented out lines for testing 



SET ANSI_NULLS ON 
GO

SET ANSI_WARNINGS ON                   
GO

-- Create user accounts
--:r G:\automation\user_accounts.sql  

-- Create restores
--:r G:\automation\db_restores.sql   
:r G:\automation\jasrestore.txt

-- fix users
--:r G:\automation\change_logins_autofix.sql  s

-- run change of objects script
--:r G:\automation\sql2008_scripts\master.sql  decomposed this

---:r G:\automation\sql2008_scripts\s2k08_GMWorkload.sql   
--:r G:\automation\sql2008_scripts\s2k08_CSOS.sql
--:r G:\automation\sql2008_scripts\s2k08_LCEWeb.sql
--:r G:\automation\sql2008_scripts\s2k08_TABS.sql
--:r G:\automation\sql2008_scripts\set_dbs_compatability_100.sql  

--  establish db email entries made in tables
--:r G:\automation\create_dbmail.sql  

-- make sure each database email script has been changed correctly
--:r G:\automation\s2k08_EMAIL_ALLDBS.sql

--  perform all link servers link server made all passwords
--:r G:\automation\link_servers.sql  

delete G:\automation\jasrestore.txt <---- just added this line for cleanup
more ▼

answered Jun 13, 2011 at 05:43 AM

yitzstokes1 gravatar image

yitzstokes1
11 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x46

asked: Jun 10, 2011 at 10:15 AM

Seen: 1419 times

Last Updated: Jun 10, 2011 at 11:27 AM