July 24, 2008 at 2:59 am
Hi,
is there a way to update the script that i will tell where i want to put the LDF and MDF files if i want to separate them?
THX
July 30, 2008 at 1:45 pm
It is said this will work for .BAK files...can this be used for .dmp files as well and can anyone help me with that...i have 200 db's to migrate to SQL 2005 and was looking for a script to generate the restore db script for me....appreciate any help...ty.
July 31, 2008 at 10:19 am
Sorry for the late reply 'm so busy in my work. Maybe i can do something to modify the script. What do you want? different location for mdf and ldf but only 2 paths?
for example
1 directory for mdf (all mdf files)
and
1 directory for ldf (all ldf files)
"-=Still Learning=-"
Lester Policarpio
July 31, 2008 at 10:23 am
vambati (7/30/2008)
It is said this will work for .BAK files...can this be used for .dmp files as well and can anyone help me with that...i have 200 db's to migrate to SQL 2005 and was looking for a script to generate the restore db script for me....appreciate any help...ty.
Questions :
1. Migrate from sql 2000 to sql 2005? The script is not tested for server migration sql 2000 to sql 2005. It is only tested for sql 2000 to sql 2000 and sql 2005 to sql 2005
2. I think i can tweak the script to accept .dmp files as long as the backup process is a native sql backup.
3. If you are restoring sql 2005 to sql 2005 server the 2 tables mentioned in the script must be changed with the appropriate table schema for sql 2005 (see posts of pau for the 2 tables schema for 2005)
"-=Still Learning=-"
Lester Policarpio
July 31, 2008 at 12:12 pm
Lester Policarpio (7/31/2008)
Sorry for the late reply 'm so busy in my work. Maybe i can do something to modify the script. What do you want? different location for mdf and ldf but only 2 paths?for example
1 directory for mdf (all mdf files)
and
1 directory for ldf (all ldf files)
yes exactly.one directory for MDF files and other directory for LDF files.
August 3, 2008 at 7:54 pm
avipenina (7/31/2008)
Lester Policarpio (7/31/2008)
Sorry for the late reply 'm so busy in my work. Maybe i can do something to modify the script. What do you want? different location for mdf and ldf but only 2 paths?for example
1 directory for mdf (all mdf files)
and
1 directory for ldf (all ldf files)
yes exactly.one directory for MDF files and other directory for LDF files.
Please try the script below and reply if it fits your need...
/*************************************************************
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
*************************************************************/
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)
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)
--Set Values to the variables
SET @newpath_mdf = 'D:\' --new path wherein you will put the mdf
SET @newpath_ldf = 'C:\' --new path wherein you will put the ldf
SET @path = 'D:\' --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)
select * from migration_lester
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
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
"-=Still Learning=-"
Lester Policarpio
August 3, 2008 at 8:08 pm
if the conditions below apply i think you can use the script even if the backup is .dmp extension
1. MSSQL 2000
2. Backup Process is a "Native SQL Backup"
- Just change the value in the @extension variable from 'BAK' to ' your extension'
- also change this syntax
DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%'
TO
DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%your extension%'
Please reply for the result.......
"-=Still Learning=-"
Lester Policarpio
November 24, 2008 at 8:13 am
Lester,
Great idea. I need a script that does this. Has it been run/tested against SQL Server 2005? When I run this script it fails b/c the restore headeronly command returns 51 columns, but the work table has significantly fewer columns on it.
Chris.
December 9, 2008 at 2:33 am
Sorry for the late reply refer to the previous post in this thread. You will see 1 member posting the 2 table schema of the tables you are saying. Change the 2 tables with the tables provided in the said post and reply your feedback if it is working
"-=Still Learning=-"
Lester Policarpio
December 9, 2008 at 5:02 am
Thanks Lester, I ended up re-writing it so that it would insert into those tables and it worked great.
Chris.
December 11, 2008 at 6:38 pm
I'm glad it works!!! 😀
"-=Still Learning=-"
Lester Policarpio
December 15, 2008 at 11:37 am
Hi,
i use the latest script post and i get this error message
Insert Error: Column name or number of supplied values does not match table definition.
all my servers are Collation Binary (case sensitive,accent sensitive).
THX
December 16, 2008 at 6:04 pm
I think that is what causing the error since you are in case sensitive mode. Try changing the created tables with the actual table listed by Microsoft in the web then give it a try
"-=Still Learning=-"
Lester Policarpio
August 6, 2009 at 5:13 am
Hi - thanks for the script Lester, it will save me a lot of time!! This is the version I used to restore from 2000 to 2008 - some slight tweaks of the header_lester and migration_lester tables to match the outputs from the RESTORE HEADONLY and RESTORE FILELISTONLY statements. Also had to change the position of the substring function from 40 to 37 to match what I was getting back from the 'dir /od' command:
/*************************************************************
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)
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,
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,
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 int,
DifferentialBaseLSN binary,
DifferentialBaseGUID binary,
BackupTypeDescription nvarchar(50),
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:\DataFiles' --new path wherein you will put the mdf
SET @newpath_ldf = 'E:\LogFiles' --new path wherein you will put the ldf
SET @path = 'C:\Backups\' --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,37,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
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
Viewing 15 posts - 31 through 45 (of 79 total)
You must be logged in to reply to this topic. Login to reply