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

avatar 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

avatar image

Pavel Pawlowski
22.7k 10 15 26

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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

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

avatar 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.

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:

x58

asked: Jun 10, 2011 at 10:15 AM

Seen: 1758 times

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

Copyright 2016 Redgate Software. Privacy Policy