February 2, 2009 at 6:48 pm
Declare
@logical_name1 varchar(80),
@logical_name2 varchar(80),
@Physical_path1 varchar(80),
@Physical_path2 varchar(80),
@move_path1 varchar(500),
@move_path2 varchar(500),
@from_path varchar(80),
@dbname as varchar(80),
@msgdb as varchar(80),
@path as varchar(80),
@table_name as varchar(80),
@dbbkpname as varchar(80),
@cmdcopy as varchar(200),
@batch as int,
@table_count as varchar(80),
@result as int,
@notes varchar(500),
@mdf_size varchar(50),
@ldf_size varchar(50)
create table #files_PACKERS4
(
RowID int identity (1,1) NOT NULL ,
LogicalName varchar(100) NULL,
PhysicalName varchar(100) NULL ,
Type varchar(10) NULL,
FileGroupName varchar(100) NULL,
varchar(20),
[maxsize] varchar(100))
set @batch = datepart(wk, getdate())
declare rs_cursor_PACKERS4 CURSOR for select name from PACKERS4.master.sys.sysdatabases where name not like 'PACKERS_%' and name <> 'tempdb' and name <> 'master' order by name
open rs_cursor_PACKERS4
Fetch next from rs_cursor_PACKERS4 into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
print '--->testing backup of '+@dbname
set @notes = 'SUCCESS'
select @Physical_path1='E:\SQLdata\PACKERS4-'+@dbname+'_Data.mdf' , @Physical_path2 ='E:\SQLdata\PACKERS4-'+@dbname+'_Log.ldf'
select @cmdcopy = 'copy \\PAREPOSITORY\PACKERS4\Full\'+@dbname+'7.bak E:\Backup\PACKERS4-testing-backup7.bak'
exec @result = xp_cmdshell @cmdcopy
if @result <> 0
Begin
insert into backupFiles..Database_Test_Results (server, dbname, batch, table_count, datatime, status, Notes ) values ('PACKERS4', @dbname, @batch, '0', getdate(), 'error','Could not copy')
FETCH NEXT FROM rs_cursor_PACKERS4 INTO @dbname
continue
end
IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = 'PACKERS4_test1' )
BEGIN
DROP DATABASE PACKERS4_test1
END
select @from_path = 'E:\Backup\PACKERS4-testing-backup7.bak'
select @table_count = '0'
insert into #files_PACKERS4 exec master.dbo.xp_restore_filelistonly @filename = @from_path
select @logical_name1 = Logicalname from #files_PACKERS4 where rowid = 1
select @logical_name2 = Logicalname from #files_PACKERS4 where rowid = 2
truncate table #files_PACKERS4
select @move_path1 = 'MOVE N'''+ @logical_name1 +''' TO N'''+@Physical_path1+''''
select @move_path2 = 'MOVE N'''+ @logical_name2 +''' TO N'''+@Physical_path2+''''
exec @result = master.dbo.xp_restore_database @database = 'PACKERS4_test1',
@filename = @from_path,
@filenumber = 1, @with = 'NOUNLOAD', @with = 'REPLACE',
@with = 'STATS = 10',
@with = @move_path1 ,
@with = @move_path2
if @result = 0
BEGIN
select @table_count = count(*) from PACKERS4_test1..sysobjects where type = 'U'
EXEC @result = sp_dboption 'PACKERS4_test1' , 'single user', 'TRUE'
END
if @result <> 0
set @notes = 'Failed'
CREATE TABLE #paths(RowID int identity (1,1) NOT NULL , Path VARCHAR(8000))
INSERT #paths(Path)
EXEC xp_cmdshell 'dir E:\Backup\PACKERS4-testing-backup7.bak'
SELECT @cmdcopy = rtrim(SUBSTRING(path,1,20)) + 'm' FROM #paths where RowID = 6
DROP TABLE #paths
set @move_path1 = 'dir ' + @Physical_path1
CREATE TABLE #paths1(RowID int identity (1,1) NOT NULL , Path VARCHAR(8000))
INSERT #paths1(Path)
EXEC xp_cmdshell @move_path1
SELECT @mdf_size = SUBSTRING(path,21,19) FROM #paths1 where RowID = 6
DROP TABLE #paths1
set @move_path2 = 'dir ' + @Physical_path2
CREATE TABLE #paths2(RowID int identity (1,1) NOT NULL , Path VARCHAR(8000))
INSERT #paths2(Path)
EXEC xp_cmdshell @move_path2
SELECT @ldf_size = SUBSTRING(path,21,19) FROM #paths2 where RowID = 6
DROP TABLE #paths2
insert into backupFiles..Database_Test_Results (server, dbname, batch, table_count, datatime, status, Notes, DateBackup, mdf_size, ldf_size) values ('PACKERS4', @dbname, @batch, @table_count, getdate(), 'Done', @notes, @cmdcopy, @mdf_size, @ldf_size)
FETCH NEXT FROM rs_cursor_PACKERS4 INTO @dbname
END
CLOSE rs_cursor_PACKERS4
DROP DATABASE PACKERS4_test1
exec xp_cmdshell 'del E:\Backup\PACKERS4-testing-backup7.bak'
deallocate rs_cursor_PACKERS4
drop table #files_PACKERS4
GO
I am using the above script for my backup strategy. But this is not working if i have multiple ndf files. So far i had just an mdf file and ndf file but now i moved into partioned databases in 2005 and that has the data split into multiple ndf files . each file for 1 months data respectively.
How can i use this kind of script to fit into my environment, please share your scripts if you have any with similar functioanlity.
thanks
February 2, 2009 at 9:15 pm
As to your code, it seems that you'd have to account for the possibility of an .ndf from the filelistonly as I don't believe you are doing that. It's late and I'm tired so maybe I missed it.
As to other scripts, I have used Tara Kizer's with pretty good success and I'm sure you could dig around for ideas about an extra .ndf if it doesn't already account for that. I haven't done it personally but can't wait.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
February 3, 2009 at 7:23 am
You just need to change the restore part of the script to make use of multiple mdf and ndf files.
Take the concept from procedure listed below:
--restore filelistonly from disk='c:orthwnd.bak'
Create PROCEDURE dbo.s_restorestatement
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @sTableName SYSNAME
DECLARE @sSQL VARCHAR(1250)
DECLARE @sSQL1 VARCHAR(1250)
DECLARE @iRowCount INT
DECLARE @LogicalNameSYSNAME
DECLARE @PhysicalNameSYSNAME
create table #TableNamesTemp
(LogicalName SYSNAME,PhysicalName SYSNAME,Type SYSNAME,FileGroupName SYSNAME NULL,
Size1 SYSNAME,MaxSize SYSNAME)
create table #temp (FileNames SYSNAME)
-- DECLARE @t_TableNames_Temp TABLE
-- (table_name SYSNAME)
-- INSERT @t_TableNames_Temp
-- restore filelistonly from disk='c:orthwnd.bak'
-- ORDER BY name
--Getting row count from table
-- SELECT @iRowCount = COUNT(*) FROM @t_TableNames_Temp
-- WHILE @iRowCount > 0
-- BEGIN
-- SELECT @sTableName = table_name from @t_TableNames_Temp
SELECT @sSQL = 'restore filelistonly from disk=''c:orthwnd.bak'''
INSERT #TableNamesTemp
EXEC (@sSQL)
SELECT @iRowCount = COUNT(*) FROM #TableNamesTemp
SELECT @sSQL = 'restore Database DBNAME from disk=''c:orthwnd.bak'' with '
WHILE @iRowCount > 0
BEGIN
SELECT @LogicalName = LogicalName from #TableNamesTemp
SELECT @PhysicalName = PhysicalName from #TableNamesTemp
Select @sSQL1='Move '+''''+@LogicalName+''''+' to '+''''+@PhysicalName+''''+','
Insert #temp values (@sSQL1)
DELETE FROM #TableNamesTemp WHERE @LogicalName = LogicalName
SELECT @iRowCount = @iRowCount - 1
END
Select @sSQL=@sSQL+FileNames from #temp
Select @sSQL=@sSQL+'STATS=20'
--Select @sSQL=reverse(@sSQL)
--SELECT STUFF(@sSQL,1,1,'''')
--Select @sSQL=reverse(@sSQL)
EXEC (@sSQL)
SET NOCOUNT OFF
GO
MJ
March 12, 2009 at 5:29 pm
Manu
I didnt get how you want me to replace the restore script, I am looking something with similiar functioanlity and more over when i tried restore filelistonly i didnt come up with all the ndf files (136).
I get this error whenei try RESTORE FILELISTONLY FROM DISK = ' D:\Backup\REV.bak'
Msg 3241, Level 16, State 0, Line 1
The media family on device 'D:\Backup\DCC_VA7.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
I think sql server allows only 64 files, am i right??
thanks
March 17, 2009 at 6:42 am
Unless I'm mistaken you are using LiteSpeed for your backups and then using a native SQL restore command to try to get the file list. LiteSpeed has its own file list command that you should be using.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply