May 23, 2008- Solved the problem when RESTORE HEADERONLY produces more than 1 value

September 29,2011- Update script to include backups in sub directories- Include search string for detecting specific backups - i.e. finding backups with specific year only

--=LIMITATIONS=--- This script is tested for backup files ".BAK" only -- SCRIPT NEEDS TO BE ALTERED IF BACKUP EXTENSION IS NOT ".BAK"*************************************************************/SET NOCOUNT ON--Drop Tables if it exists in the databaseif exists (select name from sysobjects where name = 'migration_lester')DROP TABLE migration_lesterif exists (select name from sysobjects where name = 'header_lester')DROP TABLE header_lesterif exists (select name from sysobjects where name = 'cmdshell_lester')DROP TABLE cmdshell_lester

/****************************SCRIPT FOR SUB DIRECTORIES****************************/--Drop Tables if it exists in the databaseif exists (select name from sysobjects where name = 'cmdshell_lester_subdir')DROP TABLE cmdshell_lester_subdir

--Insert the value of the command shell to the tableINSERT INTO cmdshell_lester_subdir exec master..xp_cmdshell @pathension--Delete data not equal to directoryDELETE FROM cmdshell_lester_subdir WHERE FEntry NOT LIKE ' Directory of%' DELETE FROM cmdshell_lester_subdir WHERE FEntry is NULL

--Insert the value of the command shell to the table--INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension

--Delete non backup files data, delete null valuesDELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%' DELETE FROM cmdshell_lester WHERE FEntry is NULL--Create a cursor to scan all backup files needed to generate the restore scriptDECLARE @migrate varchar(1024),@directory varchar(1000)DECLARE migrate CURSOR FORselect substring(FEntry,40,50) as 'FEntry',directory from cmdshell_lester OPEN migrateFETCH NEXT FROM migrate INTO @migrate,@directoryWHILE (@@FETCH_STATUS = 0)BEGIN--Added feature to get the dbname of the backup fileSET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@directory+'\'+@Migrate+''''INSERT INTO header_lester exec (@header)--Get the names of the mdf and ldfset @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@directory+'\'+@migrate+''''INSERT INTO migration_lester EXEC (@restore)--Update value of the table to add the new path+mdf/ldf namesUPDATE migration_lester SET physicalname = reverse(physicalname)UPDATE migration_lester SET physicalname = substring(physicalname,1,charindex('\',physicalname)-1)

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-- Run print @restoredb first to view the databases to be restored-- When ready, run exec (@restoredb)-- EXEC (@restoredb)

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@--Clear data inside the tables to give way for the next --set of informations to be put in the @restoredb variable TRUNCATE TABLE migration_lesterTRUNCATE TABLE header_lesterFETCH NEXT FROM migrate INTO @migrate,@directoryENDCLOSE migrateDEALLOCATE migrate--@@@@@@@@@@@@@@@@@@@

Sailor here is the script which crawl up to all the subdirectories in the path specified. Do note that this is for VERSION 2000 OF SQL coz I dont have dev version 2k5. Just change the table structure for it to be useful for version 2k5. Test it first in your development server as I am not liable if you run this to your prod server right away. Reply to this thread if you received any errors or if ever you have any questions or clarification hope this script will be helpful to you as you have many backups in each sub directories . I also added a search string capability so that if you want backups with specific names then you can only select those files.

Hello Sir, I am using SQL 2008 and this beautiful script should help me a great deal as we are migrating from SQL 2008 and SQL 2008 R2I keep getting the errorMsg 213, Level 16, State 7, Line 1Column name or number of supplied values does not match table definition.Msg 3013, Level 16, State 1, Line 1Msg 213, Level 16, State 7, Line 1Column name or number of supplied values does not match table definition.Msg 3013, Level 16, State 1, Line 1RESTORE HEADERONLY is terminating abnormally.Msg 213, Level 16, State 7, Line 1 is terminating abnormally.Msg 213, Level 16, State 7, Line 1Which I think it might the output of RESTORE HEADERONLY does not fit into the scheme defined? Do you have updated script for SQL 2008?Please helpGreatly appreciated

Thanks for the script, it is very helpfull, I just have one doubt, since i haven't gone deep into the script, is this script taking the last differential backups? in my case I'm doing differential backups every day and a full backup on sundays, a new backup file is made once a new week starts, following this name structure: YYYYMMDD_HHMM_NumerofWeekInTheYear_DBNAME.BAK. EXAMPLE: 20110807_1000_42_Employees.bak; therefore, one backup file has 6 differential backups and 1 full backup, that's why I'd like to know if it is getting the most recent differential backup or if I need to modify the script ... sorry if I misspelled some words, I'm not an english speaker, and I'm also starting into the Databases world.

pakwichek (11/10/2011)Hello Sir, I am using SQL 2008 and this beautiful script should help me a great deal as we are migrating from SQL 2008 and SQL 2008 R2I keep getting the errorMsg 213, Level 16, State 7, Line 1Column name or number of supplied values does not match table definition.Msg 3013, Level 16, State 1, Line 1Msg 213, Level 16, State 7, Line 1Column name or number of supplied values does not match table definition.Msg 3013, Level 16, State 1, Line 1RESTORE HEADERONLY is terminating abnormally.Msg 213, Level 16, State 7, Line 1 is terminating abnormally.Msg 213, Level 16, State 7, Line 1Which I think it might the output of RESTORE HEADERONLY does not fit into the scheme defined? Do you have updated script for SQL 2008?Please helpGreatly appreciated

Thanks for the script, it is very helpfull, I just have one doubt, since i haven't gone deep into the script, is this script taking the last differential backups? in my case I'm doing differential backups every day and a full backup on sundays, a new backup file is made once a new week starts, following this name structure: YYYYMMDD_HHMM_NumerofWeekInTheYear_DBNAME.BAK. EXAMPLE: 20110807_1000_42_Employees.bak; therefore, one backup file has 6 differential backups and 1 full backup, that's why I'd like to know if it is getting the most recent differential backup or if I need to modify the script ... sorry if I misspelled some words, I'm not an english speaker, and I'm also starting into the Databases world.

Hi hiram,

The order of the backup file is based on the result of the cmdshell operation in the script. You can try running the script (just dont uncomment EXEC (@restoredb)) and you will see which backup will be restored first. Compare the result to the folder where the backup resides.

hiram.osiris (12/26/2011)Hello, I've gone deep into the script and I made some changes in order to take the last full and differential backup made. It still needs some readjustments but by now it is helping a lot :D.

Thank you again for your Script and If you want i can share the modified script

Hello again, this is the modified script, I just made some readjustments in order to do what I needed. It still needs some readjustments. Here's the scenario: We are doing Backups every day of our working DB's at 1:00am. Only on Sundays a Fullbackup is performed and the next days a differential backup is made. Therefore, a backup file is created every week containing a Fullbackup and six differential backups. There was a situation on wich i needed to restore the most recent fullbackup and differential, that's why i made such changes to the script.

As you can see I replicated the steps where the fullbackup script is made and the differential but performing an EXEC at the end of each one.

I know I'm doing somethings wrong, or that some other parts of the code could be improved, so please I would appreciate any suggestion and comments about it.

Thanks!

USE master

SET NOCOUNT ON

--Drop Tables if it exists in the database

IF exists (SELECT name FROM sysobjects WHERE name = 'migration')DROP TABLE migration

IF exists (SELECT name FROM sysobjects WHERE name = 'header')DROP TABLE header

IF exists (SELECT name FROM sysobjects WHERE name = 'cmdshell')DROP TABLE cmdshell

/*Create Tables(cmdshell table for the cmdshell command)(migration table for the restore filelistonly command)(header table for the restore headeronly command)*/

SET @newpath_mdf = 'E:\somefolder\MSSQL\DATA\' --Specify the new path wherein you will put the mdfSET @newpath_ldf = 'E:\somefolder\MSSQL\DB Log\' --Specify the new path wherein you will put the ldfSET @path = 'E:\BACKUPS\' --Specify the path of the Backup FilesSET @extension = 'BAK' --Extention of the BackupsSET @pathension = 'dir /OD '+@Path+'*.'+@Extension --Store the shell command to retreive all .BAK filesSET @DATE = (CONVERT(VARCHAR,DATEPART(DAY,GETDATE()))) + '/' +(CONVERT(VARCHAR,DATEPART(MONTH,GETDATE()))) --Gets the current day and month

--Insert the value of the command shell to the table

INSERT INTO cmdshell EXEC master..xp_cmdshell @pathension

--Delete non backup file and null values

DELETE FROM cmdshell WHERE FEntry NOT LIKE '%.BAK%'DELETE FROM cmdshell WHERE FEntry is NULL

-- I added this part to eliminate the backups that were not made the current dayDELETE FROM cmdshellWHERE FEntry NOT LIKE @DATE + '%'

--Create a cursor to scan all backup files needed to generate the restore script

OPEN multiple FETCH NEXT FROM multiple INTO @multiple,@physical WHILE(@@FETCH_STATUS = 0) BEGIN SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+'' FETCH NEXT FROM multiple INTO @multiple,@physical END CLOSE multiple DEALLOCATE multiple

--Declare variables and cursor for getting logical and physicalname of the mdf, ldf and ndf files

DECLARE @multiple2 varchar(1000),@physical2 varchar(1000) DECLARE multiple2 CURSOR FOR SELECT logicalname,physicalname from migration OPEN multiple2 FETCH NEXT FROM multiple2 INTO @multiple2,@physical2 WHILE(@@FETCH_STATUS = 0) BEGIN SET @restoredb= @restoredb+''''+@multiple2+''''+' TO '+''''+@physical2+''''+','+'MOVE '+'' FETCH NEXT FROM multiple2 INTO @multiple2,@physical2 END CLOSE multiple2 DEALLOCATE multiple2 END SET @restoredb = substring(@restoredb,1,len(@restoredb)-5) PRINT (@restoredb) END

EXEC (@restoredb) -- Run print @restoredb first to view the differential backups to be restored -- When ready, run exec (@restoredb)

--Clear data inside the tables to give way for the next --set of informations to be put in the @restoredb variable

hiram.osiris (12/27/2011)Hello again, this is the modified script, I just made some readjustments in order to do what I needed. It still needs some readjustments. Here's the scenario: We are doing Backups every day of our working DB's at 1:00am. Only on Sundays a Fullbackup is performed and the next days a differential backup is made. Therefore, a backup file is created every week containing a Fullbackup and six differential backups. There was a situation on wich i needed to restore the most recent fullbackup and differential, that's why i made such changes to the script.

As you can see I replicated the steps where the fullbackup script is made and the differential but performing an EXEC at the end of each one.

I know I'm doing somethings wrong, or that some other parts of the code could be improved, so please I would appreciate any suggestion and comments about it.

Thanks!

USE master

SET NOCOUNT ON

--Drop Tables if it exists in the database

IF exists (SELECT name FROM sysobjects WHERE name = 'migration')DROP TABLE migration

IF exists (SELECT name FROM sysobjects WHERE name = 'header')DROP TABLE header

IF exists (SELECT name FROM sysobjects WHERE name = 'cmdshell')DROP TABLE cmdshell

/*Create Tables(cmdshell table for the cmdshell command)(migration table for the restore filelistonly command)(header table for the restore headeronly command)*/

SET @newpath_mdf = 'E:\somefolder\MSSQL\DATA\' --Specify the new path wherein you will put the mdfSET @newpath_ldf = 'E:\somefolder\MSSQL\DB Log\' --Specify the new path wherein you will put the ldfSET @path = 'E:\BACKUPS\' --Specify the path of the Backup FilesSET @extension = 'BAK' --Extention of the BackupsSET @pathension = 'dir /OD '+@Path+'*.'+@Extension --Store the shell command to retreive all .BAK filesSET @DATE = (CONVERT(VARCHAR,DATEPART(DAY,GETDATE()))) + '/' +(CONVERT(VARCHAR,DATEPART(MONTH,GETDATE()))) --Gets the current day and month

--Insert the value of the command shell to the table

INSERT INTO cmdshell EXEC master..xp_cmdshell @pathension

--Delete non backup file and null values

DELETE FROM cmdshell WHERE FEntry NOT LIKE '%.BAK%'DELETE FROM cmdshell WHERE FEntry is NULL

-- I added this part to eliminate the backups that were not made the current dayDELETE FROM cmdshellWHERE FEntry NOT LIKE @DATE + '%'

--Create a cursor to scan all backup files needed to generate the restore script

OPEN multiple FETCH NEXT FROM multiple INTO @multiple,@physical WHILE(@@FETCH_STATUS = 0) BEGIN SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+'' FETCH NEXT FROM multiple INTO @multiple,@physical END CLOSE multiple DEALLOCATE multiple

--Declare variables and cursor for getting logical and physicalname of the mdf, ldf and ndf files

DECLARE @multiple2 varchar(1000),@physical2 varchar(1000) DECLARE multiple2 CURSOR FOR SELECT logicalname,physicalname from migration OPEN multiple2 FETCH NEXT FROM multiple2 INTO @multiple2,@physical2 WHILE(@@FETCH_STATUS = 0) BEGIN SET @restoredb= @restoredb+''''+@multiple2+''''+' TO '+''''+@physical2+''''+','+'MOVE '+'' FETCH NEXT FROM multiple2 INTO @multiple2,@physical2 END CLOSE multiple2 DEALLOCATE multiple2 END SET @restoredb = substring(@restoredb,1,len(@restoredb)-5) PRINT (@restoredb) END

EXEC (@restoredb) -- Run print @restoredb first to view the differential backups to be restored -- When ready, run exec (@restoredb)

--Clear data inside the tables to give way for the next --set of informations to be put in the @restoredb variable