Convert a 2000 SP to work with 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Convert a 2000 SP to work with 2005

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-08-12 : 15:36:13

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 15:51:28

Could you at least give us a clue what's wrong with it? Is it erroring, not doing what it's supposed to do, ...?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 22:28:46

you have to enable xp_cmdshell in SQL 2005.

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-08-13 : 05:32:35

Thanks! I enabled CMD SHELL and I then realized that in 2000, if you use Maintenance plans to do backups, it names the backup file like this: pubs_db_200801191728.BAK
where as 2005 backup files look like this: pubs_Backup_200801191728.BAK

I changed the Stored Procedure to reflect this (changed it from where filename like '%_DB_%' to where filename like '%_Backup_%')

Here's the new Stored Procedure:

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


/***************************************************************************************/
-- 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 %_Backup_% 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_Backup_200305212302.BAK will be restored to
--			a database named Insurance.  The characters preceeding the '_Backup_' 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'
--

/***************************************************************************************/

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

and NOW 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.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-13 : 23:29:00

>> Column name or number of supplied values does not match table definition.

Need double check table schema and values to insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources