August 12, 2008 at 1:13 pm
I have been using this stored procedure for eons on SQL Server 2000 to recover my user databases
It's been so long I don't remember where I got it or I'de give them due credit : )
My problem is, I don't know how to modify so that it will run in 2005... CAN ANYONE HELP????
USE [Admin]
GO
/****** Object: StoredProcedure [dbo].[sp_CSS_RestoreDir] Script Date: 08/12/2008 15:10:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/***************************************************************************************/
-- Procedure Name: sp_CSS_RestoreDir
-- Purpose: Restore one or many database backups from a single directory. This script reads all
--database backups that are found in the @restoreFromDir parameter.
--Any database backup that matches the form %_db_% will be restored to
--the file locations specified in the RestoreTo... parameter(s). The database
--will be restored to a database name that is based on the database backup
--file name. For example Insurance_db_200305212302.BAK will be restored to
--a database named Insurance. The characters preceeding the '_db_' text determines
--the name.
--
-- Input Parameters: @restoreFromDir - The directory where the database backups are located
--@restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to
--@restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If
--this parameter is not provided then the log files are restored to @restoreToDataDir.
-- @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,
-- also allows for secondary data files 'ndf' to to be in a different dir than mdf files
-- @DBName - restore just this one database - selects the latest bak file
--
-- Output Parameters: None
--
-- Return Values:
--
--
-- Sample Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log'
--
-- Alternate Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', @MatchFileList = 'Y'
--
/***************************************************************************************/
CREATE proc [dbo].[sp_CSS_RestoreDir]
@restoreFromDir varchar(255),
@restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null,
@MatchFileList char(1) = 'N',
@OneDBName varchar(255) = null
as
--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
set @restoreToLogDir = @restoreToDataDir
set nocount on
SET quoted_identifier on
declare @filename varchar(255),
@cmd varchar(500),
@cmd2 varchar(500),
@DataName varchar (255),
@LogName varchar (255),
@LogicalName varchar(255),
@PhysicalName varchar(255),
@Type varchar(20),
@FileGroupName varchar(255),
@Size varchar(20),
@MaxSize varchar(20),
@restoreToDir varchar(255),
@searchName varchar(255),
@DBName varchar(255),
@PhysicalFileName varchar(255)
create table #dirList (filename varchar(100))
create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )
--Get the list of database backups that are in the restoreFromDir directory
if @OneDBName is null
select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'
else
select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'
insert #dirList exec master..xp_cmdshell @cmd
select * from #dirList where filename like '%_db_%' --order by filename
if @OneDBName is null
declare BakFile_csr cursor for
select * from #dirList where filename like '%_db_%bak' order by filename
else
begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above
select @searchName = @OneDBName + '_db_%bak'
declare BakFile_csr cursor for
select top 1 * from #dirList where filename like @searchName
end
open BakFile_csr
fetch BakFile_csr into @filename
while @@fetch_status = 0
begin
select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"
insert #filelist exec ( @cmd )
if @OneDBName is null
select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)
else
select @dbName = @OneDBName
select @cmd = "RESTORE DATABASE " + @dbName +
" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "
PRINT ''
PRINT 'RESTORING DATABASE ' + @dbName
declare DataFileCursor cursor for
select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
from #filelist
open DataFileCursor
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
while @@fetch_status = 0
begin
if @MatchFileList != 'Y'
begin -- RESTORE with MOVE option
select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))
if @Type = 'L'
select @restoreToDir = @restoreToLogDir
else
select @restoreToDir = @restoreToDataDir
select @cmd = @cmd +
" MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "
end
else
begin -- Match the file list, attempt to create any missing directory
select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )
select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir
exec master..xp_cmdshell @cmd2
end
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
end -- DataFileCursor loop
close DataFileCursor
deallocate DataFileCursor
select @cmd = @cmd + ' REPLACE, STATS = 10'
--select @cmd 'command'
EXEC (@CMD)
truncate table #filelist
fetch BakFile_csr into @filename
end -- BakFile_csr loop
close BakFile_csr
deallocate BakFile_csr
drop table #dirList
return
August 13, 2008 at 5:51 am
Had to enable CMD SHELL on 2005 and modify the SP to address the fact that 2000 backups with maintenance plans look like this: pubs_db_200801191728.BAK
where as 2005 backup files look like this: pubs_Backup_200801191728.BAK
I modified the SP to reflect this:
USE [Admin]
GO
/****** Object: StoredProcedure [dbo].[sp_CSS_RestoreDir] Script Date: 08/13/2008 05:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER proc [dbo].[sp_CSS_RestoreDir]
@restoreFromDir varchar(255),
@restoreToDataDir varchar(255)= null,
@restoreToLogDir varchar(255) = null,
@MatchFileList char(1) = 'N',
@OneDBName varchar(255) = null
as
--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
set @restoreToLogDir = @restoreToDataDir
set nocount on
SET quoted_identifier on
declare @filename varchar(255),
@cmd varchar(500),
@cmd2 varchar(500),
@DataName varchar (255),
@LogName varchar (255),
@LogicalName varchar(255),
@PhysicalName varchar(255),
@Type varchar(20),
@FileGroupName varchar(255),
@Size varchar(20),
@MaxSize varchar(20),
@restoreToDir varchar(255),
@searchName varchar(255),
@DBName varchar(255),
@PhysicalFileName varchar(255)
create table #dirList (filename varchar(100))
create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255),
Size varchar(20), MaxSize varchar(20) )
--Get the list of database backups that are in the restoreFromDir directory
if @OneDBName is null
select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'
else
select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'
insert #dirList exec master..xp_cmdshell @cmd
select * from #dirList where filename like '%_Backup_%' --order by filename
if @OneDBName is null
declare BakFile_csr cursor for
select * from #dirList where filename like '%_Backup_%bak' order by filename
else
begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above
select @searchName = @OneDBName + '_Backup_%bak'
declare BakFile_csr cursor for
select top 1 * from #dirList where filename like @searchName
end
open BakFile_csr
fetch BakFile_csr into @filename
while @@fetch_status = 0
begin
select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"
insert #filelist exec ( @cmd )
if @OneDBName is null
select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)
else
select @dbName = @OneDBName
select @cmd = "RESTORE DATABASE " + @dbName +
" FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "
PRINT ''
PRINT 'RESTORING DATABASE ' + @dbName
declare DataFileCursor cursor for
select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
from #filelist
open DataFileCursor
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
while @@fetch_status = 0
begin
if @MatchFileList != 'Y'
begin -- RESTORE with MOVE option
select @PhysicalFileName =
reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))
if @Type = 'L'
select @restoreToDir = @restoreToLogDir
else
select @restoreToDir = @restoreToDataDir
select @cmd = @cmd +
" MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "
end
else
begin -- Match the file list, attempt to create any missing directory
select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) -
patindex('%\%',reverse(@PhysicalName)) )
select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir
exec master..xp_cmdshell @cmd2
end
fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize
end -- DataFileCursor loop
close DataFileCursor
deallocate DataFileCursor
select @cmd = @cmd + ' REPLACE, STATS = 10'
--select @cmd 'command'
EXEC (@CMD)
truncate table #filelist
fetch BakFile_csr into @filename
end -- BakFile_csr loop
close BakFile_csr
deallocate BakFile_csr
drop table #dirList
return
NOW when I execute the Stored Procedure, I am getting an error:
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.
RESTORING DATABASE OFAC_backup_200807230245.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply