InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 13:33:35 GMT20http://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxIt turned out there was a typo in theSET @ndf_extension = '.ndf' statement.everything works nowThanks to all for your help!Much appreciated!Cheers,CorThu, 12 Jan 2012 00:47:14 GMTcor_perleehttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxIndeed that does seem to solve the problem except for 1 thing:Msg 8114, Level 16, State 12, Line 103Error converting data type varchar to nvarchar.it seems to be here:RESTORE DATABASE @restore_as FROM DISK = @full_backup_path WITH FILE = 1, MOVE 'U4S33_Data' TO @data_file_full_path, MOVE 'U4S33_Data1' TO @data_file_1_full_path, MOVE 'U4S33_Log' TO @log_file_full_path I tried setting @restore_as to NVARCHAR but that didn't work.I then added these satements to the scriptprint @data_file_full_pathprint @data_file_1_full_pathprint @log_file_full_path Results:D:\Data\25000994.ndfD:\Logs\25000994.ldfThe first SET statement is not shown.(.mdf file) So I think the problem is there.Cheers,CorThu, 12 Jan 2012 00:32:42 GMTcor_perleehttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxNo worries, done it plenty of times myself :)Your set statements are just setting your paths to e.g. '@data_file_path@restore_as@mdf_extension'Drop all the ' from them and you should be ok.Wed, 11 Jan 2012 09:46:06 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxI feel really stupid :)I have been looking at it all day and never saw the insert statement commented out.Thanks!!There are some more problems now but I will look at it tomorrow,Looks like the variable substitution is failing.Thanks all so far!![size="2"]Msg 5105, Level 16, State 2, Line 102A file activation error occurred. The physical file name '@data_file_path@restore_as@mdf_extension' may be incorrect. Diagnose and correct additional errors, and retry the operation.Msg 3156, Level 16, State 3, Line 102File 'U4S33_Data' cannot be restored to '@data_file_path@restore_as@mdf_extension'. Use WITH MOVE to identify a valid location for the file.Msg 5105, Level 16, State 2, Line 102A file activation error occurred. The physical file name '@data_file_1_path@restore_as@ndf_extension' may be incorrect. Diagnose and correct additional errors, and retry the operation.Msg 3156, Level 16, State 3, Line 102File 'U4S33_Data1' cannot be restored to '@data_file_1_path@restore_as@ndf_extension'. Use WITH MOVE to identify a valid location for the file.Msg 5105, Level 16, State 2, Line 102A file activation error occurred. The physical file name '@log_file_path@restore_as@ldf_extension' may be incorrect. Diagnose and correct additional errors, and retry the operation.Msg 3156, Level 16, State 3, Line 102File 'U4S33_Log' cannot be restored to '@log_file_path@restore_as@ldf_extension'. Use WITH MOVE to identify a valid location for the file.Msg 3119, Level 16, State 1, Line 102Problems were identified while planning for the RESTORE statement. Previous messages provide details.Msg 3013, Level 16, State 1, Line 102RESTORE DATABASE is terminating abnormally.[/size]Wed, 11 Jan 2012 09:32:33 GMTcor_perleehttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxAlso, if that insert statement is commented out, you'll never enter the WHILE loop.Wed, 11 Jan 2012 09:26:40 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxHi,Same as before I am afraid.(2 row(s) affected)The values in the temporary database are shown. Nothing more.cheers,CorWed, 11 Jan 2012 09:25:42 GMTcor_perleehttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxPlease alter your script with following & let us know the result.[code="sql"]--IF @@ROWCOUNT = 2 IF 1 = 1[/code]Wed, 11 Jan 2012 09:21:14 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxHi ,Actually I added serveral ' print step(n) ' to the script to see how far it would go.It stops short of this part:-- ** -------------------------------------** -- DECLARE @max_rows INT, @row_count INT SET @row_count = 1 SELECT @max_rows=count(*) FROM @DATABASES_TO_RESTORE The piece that procedes it works just fine. SELECT '25000994', '25000994' UNION SELECT '25001905', '25001905'A select on the temporary database shows the values. But after this nothing is printed anymore despite the print statements that follow.cheers, CorWed, 11 Jan 2012 09:16:06 GMTcor_perleehttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxLooking at the restore command itself, logically it makes sense. You're going to have to break down & issue print statements to validate that everything is working. Instead of trying to run the restores, just issue a select statement so that you see all the data coming back and you can ensure that it's correct to fill in the properties for the restore statement. Then try using the data in a single restore statement. Nothing is jumping out as being especially problematic.Wed, 11 Jan 2012 09:12:11 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxNow I agree with your approach. Please add a PRINT statement in IF block for debugging and post the results.[code="sql"]IF @@ROWCOUNT = 2[/code]Wed, 11 Jan 2012 09:08:33 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxActually it os somewhere between 300 and 700Cheers,CorWed, 11 Jan 2012 08:57:44 GMTcor_perleehttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxIf it’s less than 20, I would still prefer writing individual restore commands else your approach.Wed, 11 Jan 2012 08:55:31 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxHi,The databases are for a cloud invironment. Al large number of customers will have to be created on the databaseserver.The customerdatabases will be delivered as .bak files.Cheers,CorWed, 11 Jan 2012 08:44:55 GMTcor_perleehttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxUsually, we backup all databases with a script. Restore is usually per database (as on need). Why do you want to restore all databases? How many databases you need to restore with this script? Copying from blogs & running it blindly on PROD server is very dangerous. Test your scripts on Test Server first.Wed, 11 Jan 2012 08:35:24 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1234029-391-1.aspxHi all,I have been working on this script all day trying to get it to work.Just can't figure it out. There are no error messages but nothing happens.The script was copied of the internet and adjusted for my purposes. I have a large number of database that will have to be created soon. All I will get is a folder with a llot of backupfiles and possibly a list with databasenames .Any help would be greatly appreciated,-- Use VARCHAR as the restore statement doesn't like NVARCHAR DECLARE @data_file_path VARCHAR(512), @data_file_1_path VARCHAR(512), @log_file_path VARCHAR(512), @backup_path VARCHAR(512), @backup_extension VARCHAR(4), @mdf_extension VARCHAR(4), @ndf_extension VARCHAR(4), @ldf_extension VARCHAR(4) -- ** VARIABLES THAT MUST BE SET **-- SET @data_file_path = 'D:\Data\' SET @data_file_1_path = 'D:\Data\' SET @log_file_path = 'D:\Logs\' SET @backup_path = 'D:\Klantenmap\' -- **----------------------------**-- SET @backup_extension = '.bak' SET @mdf_extension = '.mdf' SET @mdf_extension = '.ndf' SET @ldf_extension = '.ldf' DECLARE @DATABASES_TO_RESTORE TABLE (rownum int IDENTITY (1,1) PRIMARY KEY NOT NULL, backup_name VARCHAR(64), restore_as VARCHAR(64)); -- ** Declare the Databases to be Restored ** -- INSERT INTO @DATABASES_TO_RESTORE SELECT '25000994', '25000994' UNION SELECT '25001905', '25001905' -- ** -------------------------------------** -- DECLARE @max_rows INT, @row_count INT SET @row_count = 1 SELECT @max_rows=count(*) FROM @DATABASES_TO_RESTORE WHILE @row_count <= @max_rows BEGIN DECLARE @backup_name VARCHAR(32) , @restore_as VARCHAR(32) , @logical_data_name VARCHAR(64) , @logical_data_1_name VARCHAR(64) , @logical_log_name VARCHAR(64) , @data_file_full_path VARCHAR(512) , @data_file_1_full_path VARCHAR(512) , @log_file_full_path VARCHAR(512) , @full_backup_path VARCHAR(MAX) , @cmd VARCHAR(128) SELECT @backup_name = backup_name, @restore_as = restore_as FROM @DATABASES_TO_RESTORE WHERE rownum = @row_count SET @full_backup_path = @backup_path + @backup_name + @backup_extension DECLARE @filelist TABLE (LogicalName NVARCHAR(128) NOT NULL, PhysicalName NVARCHAR(260) NOT NULL, [Type] CHAR(1) NOT NULL, FileGroupName NVARCHAR(120) NULL, Size NUMERIC(20, 0) NOT NULL, MaxSize NUMERIC(20, 0) NOT NULL, FileID BIGINT NULL, CreateLSN NUMERIC(25,0) NULL, DropLSN NUMERIC(25,0) NULL, UniqueID UNIQUEIDENTIFIER NULL, ReadOnlyLSN NUMERIC(25,0) NULL , ReadWriteLSN NUMERIC(25,0) NULL, BackupSizeInBytes BIGINT NULL, SourceBlockSize INT NULL, FileGroupID INT NULL, LogGroupGUID UNIQUEIDENTIFIER NULL, DifferentialBaseLSN NUMERIC(25,0)NULL, DifferentialBaseGUID UNIQUEIDENTIFIER NULL, IsReadOnly BIT NULL, IsPresent BIT NULL, TDEThumbprint VARBINARY(32) NULL) INSERT into @filelist EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @full_backup_path + '''') IF @@ROWCOUNT = 2 BEGIN --SELECT @logical_data_name = LogicalName FROM @filelist WHERE [Type] = 'D' --SELECT @logical_log_name = LogicalName FROM @filelist WHERE [Type] = 'L' SET @data_file_full_path = '@data_file_path' + '@restore_as' + '@mdf_extension' SET @data_file_1_full_path = '@data_file_1_path' + '@restore_as' + '@ndf_extension' SET @log_file_full_path = '@log_file_path' + '@restore_as' + '@ldf_extension' RESTORE DATABASE @restore_as FROM DISK = @full_backup_path WITH FILE = 1, MOVE N'U4S33_Data' TO @data_file_full_path, MOVE N'U4S33_Data1' TO @data_file_1_full_path, MOVE N'U4S33_Log' TO @log_file_full_path END ELSE PRINT 'CANNOT RESTORE DATABASE ' + @restore_as + ' THE BACKUP CONTAINS MORE THAN 1 BACKUP SET' SELECT @row_count = @row_count + 1 END Wed, 11 Jan 2012 08:22:34 GMTcor_perlee