September 28, 2009 at 8:21 pm
it's been soooooooo long since I last open my account :hehe: thanks for sharing your idea at least it is proven to run sql 2000 to sql 2008 restore procedure nice.... 😀
"-=Still Learning=-"
Lester Policarpio
May 21, 2010 at 6:10 am
After consulting Steven via a PM, I changed the script a little bit to let it work with at least my 2008 edition. Here it is:
/*************************************************************
Script made by : Lester A. Policarpio
Email Address : lpolicarpio2001@yahoo.com
Date Created : September 03, 2007
--=UPDATES=--
May 21, 2010
- More adjustments for SQL Server 2008:
- In table header_lester "DifferentialBasLsn binary" between columns "CheckpointLsn binary"
and "BackupStartDate datetime" replaced by "DatabaseBackupLSN binary,";
- In the same table "RecoveryModel int" changed to "RecoveryModel nvarchar(60)".
August 6, 2009 by Steven Webster
- Adjusted for SQL Server 2008
(Note created by Sjors Takes)
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
--=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))
CREATE TABLE migration_lester(
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_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,
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)
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 = 'D:\Data' --new path wherein you will put the mdf
SET @newpath_ldf = 'E:\Log' --new path wherein you will put the ldf
SET @path = 'E:\RestoreLester\' --Path of the Backup File
SET @extension = 'BAK'
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension
--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)
DECLARE migrate CURSOR FOR
select substring(FEntry,40,50) as 'FEntry'from cmdshell_lester
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate
WHILE (@@FETCH_STATUS = 0)BEGIN
--Added feature to get the dbname of the backup file
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO header_lester exec (@header)
--Get the names of the mdf and ldf
set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@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 = '+ ''''+@path+@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
print @migrate
SET @restoredb =
'RESTORE DATABASE '+(select top 1 DatabaseName from header_lester)+
' FROM DISK = '+''''+@path+@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
END
CLOSE migrate
DEALLOCATE migrate
--@@@@@@@@@@@@@@@@@@@
--Drop Tables
DROP TABLE migration_lester
DROP TABLE cmdshell_lester
DROP TABLE header_lester
August 18, 2010 at 7:17 pm
Thanks Stakes for the comment 🙂
"-=Still Learning=-"
Lester Policarpio
September 27, 2010 at 5:36 am
Hi lester,
Great script. it works great. just have to make sure the backup directory does not contain space.i.e c:\program files\Microsoft SQLserver\ will not work for me until i changed it to c:\backup
February 25, 2011 at 6:02 am
Hi,
No matter how my backups were named I get the same errors:
Msg 213, Level 16, State 7, Line 1
Insert Error: 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.
and:
Msg 213, Level 16, State 7, Line 1
Insert Error: 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.
Then I went to the forum and found that I need to use a bit different table structures for two of the tables.
That worked fine on my SQL Server 2005!
Thanks a lot to all the contributors and to Lester - the original author!
June 15, 2011 at 5:00 am
i used the script and modified these below lines...
SET @newpath_mdf = 'D:\DataFiles' --new path wherein you will put the mdf
SET @newpath_ldf = 'E:\LogFiles' --new path wherein you will put the ldf
SET @path = 'D:\downloading\test\' --Path of the Backup File
but iam getting this error "Cannot open backup device 'D:\downloading\test\36 AngrauDoamr_db_201106141035.BAK'. Operating system error 2(The system cannot find the file specified.).
"
actually my db name is "Angrau_Doamr_20110614.BAK" i renamed it to "AngrauDoamr_db_201106141035.BAK"...
can you give me a solution as soon as possible
June 15, 2011 at 5:36 am
Remove the last backslash in your path for the backup file, i.e.:
SET @path = 'D:\downloading\test' --Path of the Backup File instead of:
SET @path = 'D:\downloading\test\' --Path of the Backup File
Good luck!
Regards
August 15, 2011 at 7:17 am
All of my databases are in subdirectories so I changed
SET @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?
August 15, 2011 at 7:32 am
Obviously the file 'D:\sqlbackups\admin_backup_201108141900.bak' doesn't exist on your system. Please check if the file is present first. Is the name exactly the same as 'admin_backup_201108141900.bak'?
August 15, 2011 at 8:09 am
stakes (8/15/2011)
Obviously the file 'D:\sqlbackups\admin_backup_201108141900.bak' doesn't exist on your system. Please check if the file is present first. Is the name exactly the same as 'admin_backup_201108141900.bak'?
Again, each database backup is in a subdirectory under d:\sqlbackups. So admin will be in subdir d:\sqlbackups\admin.
August 15, 2011 at 8:23 am
Here it is for SQL 2005 using subdirectories for each database backup file:
/*************************************************************
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
--=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)
--select * from migration_lester
--select * from cmdshell_lester
CREATE TABLE cmdshell_lester( fentry varchar(1000))
CREATE TABLE migration_lester(
LogicalName varchar(1024),
PhysicalName varchar(4000),
type char(1),
FileGroupName varchar(50),
size real,
MaxSize real,
FileIDbigint,
CreateLSNnumeric(25,0),
DropLSNnumeric(25,0),
UniqueIDbinary,
ReadOnlyLSNnumeric(25,0),
ReadWriteLSNnumeric(25,0),
BackupSizeInBytesbigint,
SourceBlockSizeint,
FileGroupIDint,
LogGroupGUIDbinary,
DifferentialBaseLSNnumeric(25,0),
DifferentialBaseGUIDbinary,
IsReadOnlybit,
IsPresentbit,
)
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),
FamilyGUID binary,
HasBulkLoggedData int,
IsSnapshot int,
IsReadOnly int,
IsSingleUser int,
HasBackupChecksums int,
IsDamaged int,
BeginsLogChain int,
HasIncompleteMetaData int,
IsForceOffline int,
IsCopyOnly int,
FirstRecoveryForkIDbinary,
ForkPointLSN binary,
RecoveryModel nvarchar(50),
DifferentialBaseLSN binary,
DifferentialBaseGUID binary,
BackupTypeDescription nvarchar(50),
BackupSetGUID nvarchar(255),
)
--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 @subdir varchar(1024)
--Set Values to the variables
SET @newpath_mdf = 'e:\data\' --new path wherein you will put the mdf
SET @newpath_ldf = 'e:\data\' --new path wherein you will put the ldf
SET @path = 'D:\sqlbackups\' --Path of the Backup File
SET @extension = 'BAK'
SET @pathension = 'dir /OD/S '+@Path+'*2011081419*.'+@Extension -- specify year month day and hour
--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%' and FEntry NOT LIKE '%Directory%'
DELETE FROM cmdshell_lester WHERE FEntry is NULL
select * from cmdshell_lester
--Create a cursor to scan all backup files needed to generate the restore script
DECLARE @migrate varchar(1024)
DECLARE migrate CURSOR FOR
select Fentry as 'FEntry'from cmdshell_lester
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate
WHILE (@@FETCH_STATUS = 0)BEGIN
--Added feature to get the dbname of the backup file
--SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@Migrate+''''
set @subdir = substring(@migrate,15,50) + '\'
FETCH NEXT FROM migrate INTO @migrate
set @migrate = substring(@migrate,40,50)
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@subdir+@Migrate+''''
--print @header
INSERT INTO header_lester exec (@header)
--Get the names of the mdf and ldf
set @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@subdir+@migrate+''''
--print @restore
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 = '+ ''''+@subdir+@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 = '+''''+@path+@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
END
CLOSE migrate
DEALLOCATE migrate
--@@@@@@@@@@@@@@@@@@@
--Drop Tables
--DROP TABLE migration_lester
--DROP TABLE cmdshell_lester
--DROP TABLE header_lester
August 15, 2011 at 8:36 am
ok, I see. You should put the backup's in the same directory. That is what the script is made for. Otherwise you'll have to alter the script. But I strongly advise you to take care the backup's are in the same directory. That is the easy way...
August 15, 2011 at 8:50 am
I would have 40,000 files in that one directory. It helps to keep things tidy.
August 15, 2011 at 9:20 am
I wouldn't mind having 40.000 backup files in one directory. But that's me and it doesn't help solving your problem. So I took another look at the script. With the /S option, you try to get a list of the subdirectories, but that doesn't work. What you want is a dir option which gets a list of all *.bak files including their paths. I don't know if that option exists, you'll have to search for it (help dir). Hopefully it's there. If not, you have to alter the script in a different way to make it work.
Good luck! 🙂
btw: I'm not a scripting guy. I just altered Lester's script a while a go to make it work with SQL Server 2008.
September 28, 2011 at 7:54 pm
Thats right borce every time sql version is upgraded i.e. 2000 to 2005, I notice that they added columns on the tables used in the upgraded version. This is to ensure that restored dbs in the newer version cannot be used in the lower versions. Thats why for every versions (7,2k,2k5,2k8 etc) scripts must always be altered. Thanks also to those who updated the first script made from 2k for it to be available in newer versions.
"-=Still Learning=-"
Lester Policarpio
Viewing 15 posts - 46 through 60 (of 79 total)
You must be logged in to reply to this topic. Login to reply