September 28, 2011 at 8:05 pm
Sailor,
is this already solved? All backups are in subdirectories in d:\sqlbackups right? are they all .bak files?
I'll try to alter the script to solved the problem you are facing.
"-=Still Learning=-"
Lester Policarpio
September 28, 2011 at 10:19 pm
Sailor (8/15/2011)
All of my databases are in subdirectories so I changedSET @pathension = 'dir /OD/S '+@Path+'*20110814*.'+@Extension
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'D:\sqlbackups\admin_backup_201108141900.bak'. Operating system error 2(The system cannot find the file specified.).
So admin backup is in d:\sqlbackups\admin.
What do I need to change to get it to work?
/*************************************************************
Script made by : Lester A. Policarpio
Email Address : lpolicarpio2001@yahoo.com
Date Created : September 03, 2007
--=UPDATES=--
January 17, 2008
- Solved outputed value of the @restoredb variable
- Solved the "invalid length parameter" issue
May 6, 2008
- Removed unused variables
- Add the headeronly command to accept non sql backup format
(dbname_db_YYYYMMDDHHMM.BAK)
- Add more comments
May 12, 2008
- Accept Backup Files With Multiple NDFs
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 database
if exists (select name from sysobjects where name = 'migration_lester')
DROP TABLE migration_lester
if exists (select name from sysobjects where name = 'header_lester')
DROP TABLE header_lester
if exists (select name from sysobjects where name = 'cmdshell_lester')
DROP TABLE cmdshell_lester
--Create Tables
--(cmdshell_lester table for the cmdshell command)
--(migration_lester table for the restore filelistonly command)
--(header_lester table for the restore headeronly command)
CREATE TABLE cmdshell_lester( fentry varchar(1000),directory varchar(1000))
CREATE TABLE migration_lester(LogicalName varchar(1024),
PhysicalName varchar(4000),type char(1),FileGroupName varchar(50),
size real,MaxSize real)
CREATE TABLE header_lester (BackupName varchar(50),
BackupDescription varchar(100),BackupType int,
ExpirationDate nvarchar(50),Compressed int,Position int,
DeviceType int,UserName varchar(30),ServerName varchar(30),
DatabaseName varchar(50),DatabaseVersion int,
DatabaseCreationDate datetime,BackupSize bigint,FirstLsn binary,
LastLsn binary,CheckpointLsn binary,DifferentialBasLsn binary,
BackupStartDate datetime,BackupFinishDate datetime,SortOrder int,
CodePage int,UnicodeLocaleid int,UnicodeComparisonStyle int,
CompatibilityLevel int,SoftwareVendorId int,SoftwareVersionMajor int,
SoftwareVersionMinor int,SoftwareVersionBuild int,
MachineName varchar(50),Flags int,BindingId nvarchar(50),
RecoveryForkId nvarchar(50),Collation nvarchar(50))
--Declare Variables
DECLARE @path varchar(1024),@restore varchar(1024)
DECLARE @restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)
DECLARE @pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)
DECLARE @search_string varchar(250)
--Set Values to the variables
SET @newpath_mdf = 'C:\' --new path wherein you will put the mdf
SET @newpath_ldf = 'C:\' --new path wherein you will put the ldf
SET @path = 'C:\' --Path of the Backup File (put \ on the end)
SET @search_string = '' -- leave it blank if you dont have a search string, change if neccessary
SET @extension = 'BAK'
SET @pathension = 'dir /OD/S "'+@Path+'*.'+@Extension+'"'
/****************************
SCRIPT FOR SUB DIRECTORIES
****************************/
--Drop Tables if it exists in the database
if exists (select name from sysobjects where name = 'cmdshell_lester_subdir')
DROP TABLE cmdshell_lester_subdir
--Create Table
CREATE TABLE cmdshell_lester_subdir( fentry varchar(1000))
--Insert the value of the command shell to the table
INSERT INTO cmdshell_lester_subdir exec master..xp_cmdshell @pathension
--Delete data not equal to directory
DELETE FROM cmdshell_lester_subdir WHERE FEntry NOT LIKE ' Directory of%'
DELETE FROM cmdshell_lester_subdir WHERE FEntry is NULL
-- CREATE LOOP
DECLARE @subdir varchar(250),@subdir2 varchar(250)
DECLARE subdir CURSOR FOR
select substring(FEntry,15,len(FEntry)) as 'SUBDIR' from cmdshell_lester_subdir
OPEN subdir
FETCH NEXT FROM subdir INTO @subdir
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @subdir2 = 'dir /OD "'+@subdir+'\*'+@search_string+'*.'+@Extension+'"'
INSERT INTO cmdshell_lester (FEntry) EXEC master..xp_cmdshell @subdir2
update cmdshell_lester set directory = @subdir where directory IS NULL
FETCH NEXT FROM subdir INTO @subdir
END
CLOSE subdir
DEALLOCATE subdir
/***************************
END OF SUB DIRECTORY SCRIPT
****************************/
--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 values
DELETE 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 script
DECLARE @migrate varchar(1024),@directory varchar(1000)
DECLARE migrate CURSOR FOR
select substring(FEntry,40,50) as 'FEntry',directory from cmdshell_lester
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate,@directory
WHILE (@@FETCH_STATUS = 0)BEGIN
--Added feature to get the dbname of the backup file
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@directory+'\'+@Migrate+''''
INSERT INTO header_lester exec (@header)
--Get the names of the mdf and ldf
set @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 names
UPDATE migration_lester SET physicalname = reverse(physicalname)
UPDATE migration_lester SET physicalname =
substring(physicalname,1,charindex('\',physicalname)-1)
UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) where type = 'D'
UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) where type = 'L'
--@@@@@@@@@@@@@@@@@@@@
--Set a value to the @restoredb variable to hold the restore database script
IF (select count(*) from migration_lester) = 2
BEGIN
SET @restoredb = 'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)
+' FROM DISK = '+ ''''+@directory+'\'+@migrate+''''+' WITH MOVE '+''''
+(select logicalname from migration_lester where type = 'D')+''''
+' TO '+ ''''+( select physicalname from migration_lester WHERE physicalname like '%mdf%')
+''''+', MOVE '+''''+ (select logicalname from migration_lester where type = 'L')
+''''+' TO '+''''+( select physicalname from migration_lester
WHERE physicalname like '%ldf%')+''''
print (@restoredb)
END
IF (select count(*) from migration_lester) > 2
BEGIN
SET @restoredb =
'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+
' FROM DISK = '+''''+@directory+'\'+@migrate+''''+'WITH MOVE '
DECLARE @multiple varchar(1000),@physical varchar(1000)
DECLARE multiple CURSOR FOR
Select logicalname,physicalname from migration_lester
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
SET @restoredb = substring(@restoredb,1,len(@restoredb)-5)
print (@restoredb)
END
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- 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_lester
TRUNCATE TABLE header_lester
FETCH NEXT FROM migrate INTO @migrate,@directory
END
CLOSE migrate
DEALLOCATE migrate
--@@@@@@@@@@@@@@@@@@@
--Drop Tables
DROP TABLE migration_lester
DROP TABLE cmdshell_lester
DROP TABLE cmdshell_lester_subdir
DROP TABLE header_lester
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.
"-=Still Learning=-"
Lester Policarpio
November 10, 2011 at 10:35 pm
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 R2
I keep getting the error
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE 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 help
Greatly appreciated
pa
December 6, 2011 at 10:14 am
Hi
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. 🙂
December 25, 2011 at 7:38 pm
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 R2
I keep getting the error
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE 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 help
Greatly appreciated
Hi kindly view page 5 of this thread post of Steven Webster-494809 or stakes 🙂
P.S.
Ohh another thing, I think I browsed an article regarding incompatibility of SQL 2008 restoration to SQL 2008 R2 (or vice versa). I maybe wrong but it will not hurt you to just check and verify
"-=Still Learning=-"
Lester Policarpio
December 25, 2011 at 7:57 pm
hiram.osiris (12/6/2011)
HiThanks 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.
P.S.
Don't worry I am not a pure English speaker as well 😉
"-=Still Learning=-"
Lester Policarpio
December 26, 2011 at 7:40 pm
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
December 26, 2011 at 11:22 pm
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
Sure please for the benefit of other forumers
"-=Still Learning=-"
Lester Policarpio
December 27, 2011 at 8:40 am
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! 🙂
USEmaster
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)
*/
CREATE TABLE cmdshell( fentry VARCHAR(1000))
CREATE TABLE migration(LogicalName VARCHAR(1024),
PhysicalName VARCHAR(4000),
TYPE CHAR(1),
FileGroupName VARCHAR(50),
size REAL,
MaxSize REAL,
FileID BIGINT,
CreateLSN NUMERIC(25,0),
DropLSN NUMERIC(25,0),
UniqueID BINARY,
ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupID INT,
LogGroupGUID BINARY,
DifferentialBaseLSN NUMERIC(25,0),
DifferentialBaseGUID BINARY,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint BINARY)
CREATE TABLE header (BackupName VARCHAR(50),
BackupDescription VARCHAR(100),
BackupType INT,
ExpirationDate NVARCHAR(50),
Compressed INT,
Position CHAR(30),
DeviceType INT,
UserName VARCHAR(30),
ServerName VARCHAR(30),
DatabaseName VARCHAR(50),
DatabaseVersion INT,
DatabaseCreationDate DATETIME,
BackupSize BIGINT,
FirstLsn BINARY,
LastLsn BINARY,
CheckpointLsn BINARY,
DatabaseBackupLSN BINARY,
BackupStartDate DATETIME,
BackupFinishDate DATETIME,
SortOrder INT,
CodePage INT,
UnicodeLocaleid INT,
UnicodeComparisonStyle INT,
CompatibilityLevel INT,
SoftwareVendorId INT,
SoftwareVersionMajor INT,
SoftwareVersionMinor INT,
SoftwareVersionBuild INT,
MachineName VARCHAR(50),
Flags INT,
BindingId NVARCHAR(50),
RecoveryForkId NVARCHAR(50),
Collation NVARCHAR(50),
FamilyGUID BINARY,
HasBulkLoggedData INT,
IsSnapshot INT,
IsReadOnly INT,
IsSingleUser INT,
HasBackupChecksums INT,
IsDamaged INT,
BeginsLogChain INT,
HasIncompleteMetaData INT,
IsForceOffline INT,
IsCopyOnly INT,
FirstRecoveryForkID BINARY,
ForkPointLSN BINARY,
RecoveryModel NVARCHAR(60),
DifferentialBaseLSN BINARY,
DifferentialBaseGUID BINARY,
BackupTypeDescription NVARCHAR(60),
BackupSetGUID BINARY,
CompressedBackupSize BIGINT)
--Declare Variables
DECLARE@path varchar(1024),@restore varchar(1024),@DATE VARCHAR(6)
DECLARE@restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)
DECLARE@pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)
--Set Values to the variables
SET @newpath_mdf ='E:\somefolder\MSSQL\DATA\' --Specify the new path wherein you will put the mdf
SET @newpath_ldf ='E:\somefolder\MSSQL\DB Log\' --Specify the new path wherein you will put the ldf
SET @path ='E:\BACKUPS\' --Specify the path of the Backup Files
SET @extension ='BAK' --Extention of the Backups
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension --Store the shell command to retreive all .BAK files
SET @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 day
DELETE FROM cmdshell
WHERE FEntry NOT LIKE @DATE + '%'
--Create a cursor to scan all backup files needed to generate the restore script
DECLARE@migrate varchar(1024)
DECLAREmigrate CURSOR FOR
SELECTsubstring(FEntry,42,54) AS 'FEntry'
FROMcmdshell
--Open the cursor "migrate" to insert all backup names into @migrate
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Get the dbname of the backup file using the RESTORE HEADERONLY command
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO header
EXEC (@header)
--Get the names of the mdf and ldf files using the RESTORE FILELISTONLY command
SET @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO migration
EXEC (@restore)
--Update value of the table to add the new path+mdf/ldf names
UPDATE migration
SET physicalname = reverse(physicalname)
UPDATE migration
SET physicalname = substring(physicalname,1,charindex('\',physicalname)-1)
UPDATE migration
SET physicalname = @newpath_mdf+reverse(physicalname)
WHERE TYPE = 'D'
UPDATE migration
SET physicalname = @newpath_ldf+reverse(physicalname)
WHERE TYPE = 'L'
--Generating the script for FULL RESTORE for backups with mdf, ldf and ndf files
BEGIN
BEGIN
SET @restoredb ='RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header)+
' FROM DISK = '+''''+@path+@migrate+''''+'WITH FILE =' +''
+(SELECT MIN (Position) FROM header WHERE BackupType= 1)
+CHAR(10)+', NORECOVERY, STATS=10, NOUNLOAD, REPLACE, MOVE '
--Declare variables and cursor for getting logical and physicalname of the mdf, ldf and ndf files
DECLARE @multiple VARCHAR(1000),@physical VARCHAR(1000)
DECLARE multiple CURSOR FOR
SELECT logicalname,physicalname FROM migration
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
SET @restoredb =substring(@restoredb,1,len(@restoredb)-5)
PRINT (@restoredb)
END
EXEC (@restoredb)-- Run print @restoredb first to view the full backups to be restored
-- When ready, run exec (@restoredb)
--Generating the script for DIFFERRENTIAL RESTORE for backups with mdf, ldf and ndf files
BEGIN
SET @restoredb =
CHAR(10)+'RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header)+
' FROM DISK = '+''''+@path+@migrate+''''+'WITH FILE =' +''+(SELECT MAX (Position) FROM header WHERE BackupType= 5)
+CHAR(10)+',STATS=10, NOUNLOAD, MOVE '
--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
TRUNCATE TABLE migration
TRUNCATE TABLE header
FETCH NEXT FROM migrate INTO @migrate
END
CLOSE migrate
DEALLOCATE migrate
--Drop Tables
DROP TABLE migration
DROP TABLE cmdshell
DROP TABLE header
January 3, 2012 at 6:15 pm
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! 🙂
USEmaster
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)
*/
CREATE TABLE cmdshell( fentry VARCHAR(1000))
CREATE TABLE migration(LogicalName VARCHAR(1024),
PhysicalName VARCHAR(4000),
TYPE CHAR(1),
FileGroupName VARCHAR(50),
size REAL,
MaxSize REAL,
FileID BIGINT,
CreateLSN NUMERIC(25,0),
DropLSN NUMERIC(25,0),
UniqueID BINARY,
ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupID INT,
LogGroupGUID BINARY,
DifferentialBaseLSN NUMERIC(25,0),
DifferentialBaseGUID BINARY,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint BINARY)
CREATE TABLE header (BackupName VARCHAR(50),
BackupDescription VARCHAR(100),
BackupType INT,
ExpirationDate NVARCHAR(50),
Compressed INT,
Position CHAR(30),
DeviceType INT,
UserName VARCHAR(30),
ServerName VARCHAR(30),
DatabaseName VARCHAR(50),
DatabaseVersion INT,
DatabaseCreationDate DATETIME,
BackupSize BIGINT,
FirstLsn BINARY,
LastLsn BINARY,
CheckpointLsn BINARY,
DatabaseBackupLSN BINARY,
BackupStartDate DATETIME,
BackupFinishDate DATETIME,
SortOrder INT,
CodePage INT,
UnicodeLocaleid INT,
UnicodeComparisonStyle INT,
CompatibilityLevel INT,
SoftwareVendorId INT,
SoftwareVersionMajor INT,
SoftwareVersionMinor INT,
SoftwareVersionBuild INT,
MachineName VARCHAR(50),
Flags INT,
BindingId NVARCHAR(50),
RecoveryForkId NVARCHAR(50),
Collation NVARCHAR(50),
FamilyGUID BINARY,
HasBulkLoggedData INT,
IsSnapshot INT,
IsReadOnly INT,
IsSingleUser INT,
HasBackupChecksums INT,
IsDamaged INT,
BeginsLogChain INT,
HasIncompleteMetaData INT,
IsForceOffline INT,
IsCopyOnly INT,
FirstRecoveryForkID BINARY,
ForkPointLSN BINARY,
RecoveryModel NVARCHAR(60),
DifferentialBaseLSN BINARY,
DifferentialBaseGUID BINARY,
BackupTypeDescription NVARCHAR(60),
BackupSetGUID BINARY,
CompressedBackupSize BIGINT)
--Declare Variables
DECLARE@path varchar(1024),@restore varchar(1024),@DATE VARCHAR(6)
DECLARE@restoredb varchar(2000),@extension varchar(1024),@newpath_ldf varchar(1024)
DECLARE@pathension varchar(1024),@newpath_mdf varchar(1024),@header varchar(500)
--Set Values to the variables
SET @newpath_mdf ='E:\somefolder\MSSQL\DATA\' --Specify the new path wherein you will put the mdf
SET @newpath_ldf ='E:\somefolder\MSSQL\DB Log\' --Specify the new path wherein you will put the ldf
SET @path ='E:\BACKUPS\' --Specify the path of the Backup Files
SET @extension ='BAK' --Extention of the Backups
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension --Store the shell command to retreive all .BAK files
SET @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 day
DELETE FROM cmdshell
WHERE FEntry NOT LIKE @DATE + '%'
--Create a cursor to scan all backup files needed to generate the restore script
DECLARE@migrate varchar(1024)
DECLAREmigrate CURSOR FOR
SELECTsubstring(FEntry,42,54) AS 'FEntry'
FROMcmdshell
--Open the cursor "migrate" to insert all backup names into @migrate
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Get the dbname of the backup file using the RESTORE HEADERONLY command
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO header
EXEC (@header)
--Get the names of the mdf and ldf files using the RESTORE FILELISTONLY command
SET @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO migration
EXEC (@restore)
--Update value of the table to add the new path+mdf/ldf names
UPDATE migration
SET physicalname = reverse(physicalname)
UPDATE migration
SET physicalname = substring(physicalname,1,charindex('\',physicalname)-1)
UPDATE migration
SET physicalname = @newpath_mdf+reverse(physicalname)
WHERE TYPE = 'D'
UPDATE migration
SET physicalname = @newpath_ldf+reverse(physicalname)
WHERE TYPE = 'L'
--Generating the script for FULL RESTORE for backups with mdf, ldf and ndf files
BEGIN
BEGIN
SET @restoredb ='RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header)+
' FROM DISK = '+''''+@path+@migrate+''''+'WITH FILE =' +''
+(SELECT MIN (Position) FROM header WHERE BackupType= 1)
+CHAR(10)+', NORECOVERY, STATS=10, NOUNLOAD, REPLACE, MOVE '
--Declare variables and cursor for getting logical and physicalname of the mdf, ldf and ndf files
DECLARE @multiple VARCHAR(1000),@physical VARCHAR(1000)
DECLARE multiple CURSOR FOR
SELECT logicalname,physicalname FROM migration
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
SET @restoredb =substring(@restoredb,1,len(@restoredb)-5)
PRINT (@restoredb)
END
EXEC (@restoredb)-- Run print @restoredb first to view the full backups to be restored
-- When ready, run exec (@restoredb)
--Generating the script for DIFFERRENTIAL RESTORE for backups with mdf, ldf and ndf files
BEGIN
SET @restoredb =
CHAR(10)+'RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header)+
' FROM DISK = '+''''+@path+@migrate+''''+'WITH FILE =' +''+(SELECT MAX (Position) FROM header WHERE BackupType= 5)
+CHAR(10)+',STATS=10, NOUNLOAD, MOVE '
--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
TRUNCATE TABLE migration
TRUNCATE TABLE header
FETCH NEXT FROM migrate INTO @migrate
END
CLOSE migrate
DEALLOCATE migrate
--Drop Tables
DROP TABLE migration
DROP TABLE cmdshell
DROP TABLE header
Hi does the script give you error/s when executing? or are you already using it?
"-=Still Learning=-"
Lester Policarpio
January 4, 2012 at 8:17 am
Hi, the script is working fine, it doesn't display any errors, but I'd like to know if there are some parts of the current code that could be improved. I'd also like to add something to evaluate which are the most recent backup files, and take those for restoring, since I have backup files from several weeks in a single folder. I currently added a code to delete the backups from cmdshell table that weren't made the current day:
SET @DATE = (CONVERT(VARCHAR,DATEPART(DAY,GETDATE()))) + '/' +(CONVERT(VARCHAR,DATEPART(MONTH,GETDATE()))) --Gets the current day and month
-- I added this part to eliminate the backups that were not made the current day
DELETE FROM cmdshell
WHERE FEntry NOT LIKE @DATE + '%'
but there are sometimes on which the most recent backups are from 1, 2, or more days ago, and I'd like to dynamically get them from cmdshell table, do you have any suggestions??
Thank you very much for your time 🙂
January 4, 2012 at 6:49 pm
So what you want is to restore the latest backup? regardless if the backup is 1 or 2 days ago as long as it is the current one? are you restoring...
only 1 backup?
1 diff and 1 full?
1 full?
1. you can retain (in the cmdshell table) the max date from your script
2. you can look per filename (which is your format) or per system date (the first 20 chars of the result in cmdshell table)
"-=Still Learning=-"
Lester Policarpio
February 3, 2014 at 4:26 pm
hi :
i have one my db bkp files in format t1_db_201402031813.bak
but when i run..i am getting the following error..
any help would be greatly appreciated...
i need to restore close 200 dbs..
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
February 3, 2014 at 4:34 pm
Here is another version that should work pretty well.
http://jasonbrimhall.info/2014/01/14/t-sql-tuesday-050-automating-database-restores/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 17, 2014 at 12:49 am
What is the version of your database server?
"-=Still Learning=-"
Lester Policarpio
Viewing 15 posts - 61 through 75 (of 79 total)
You must be logged in to reply to this topic. Login to reply