October 23, 2008 at 2:58 am
Hi,
When i try to backup a database to our backup server i get the error ;
"Write on 'Backup_Device_Name' failed, status = 1130. See the SQL Server error log for more details."
It was working before. Also the other databases on the same server can be backed up. I deleted and created a new backup device but it didn't work. Did anyone see this error before? Do you have an idea.
Thanks.
October 23, 2008 at 3:55 am
I have to ask,
have you checked the SQL log?
October 23, 2008 at 6:44 am
Yes i looked. I didn't find something so different. Here's what i saw :
BackupMedium::ReportIoError: write failure on backup device '\\XX.bak'. Operating system error 1130(error not found).
any ideas ?
October 23, 2008 at 6:52 am
1. Not enough space for backup. (Most usual).
2. Remote device failure (check the disk).
October 23, 2008 at 7:22 am
nailosuper (10/23/2008)
Yes i looked. I didn't find something so different. Here's what i saw :BackupMedium::ReportIoError: write failure on backup device '\\XX.bak'. Operating system error 1130(error not found).
any ideas ?
recheck the backup device !! IMO it points to an invalid location !
(not available to the sqlserver service account !)
FYI
-- ALZDBA dd 20060622
-- Alter Local Default Backup Directory
--
Declare @NewDrive nchar(1)
Declare @RootDirectory2Add nvarchar(512)
Declare @NewDirectoryPath nvarchar(512)
Declare @Override char(1)
select @NewDrive = N'K'
, @RootDirectory2Add = ''
, @NewDirectoryPath = '' -- default blanc ! -- 'J:\MSSQL.2\MSSQL\Backup' -->@Override = Y needed !!
, @Override = upper('N')
set nocount on
declare @CmdShellSettingBefore table (cfgname varchar(128), minimum varchar(128), maximum varchar(128), config_value varchar(128), runvalue varchar(128))
insert into @CmdShellSettingBefore
EXEC sys.sp_configure N'xp_cmdshell'
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
end
-- Default-path ?
declare @DefaultBackupDirectory nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @DefaultBackupDirectory OUTPUT;
if @DefaultBackupDirectory like '_:\MSSQL.%'
or @Override = 'Y'
begin
if @NewDrive = N'' set @NewDrive = substring(@DefaultBackupDirectory,1,1)
print N'-- OLD path ' + @DefaultBackupDirectory
if @Override = 'Y'
and @NewDirectoryPath != ''
begin
set @DefaultBackupDirectory = @RootDirectory2Add
end
else
begin
select @DefaultBackupDirectory = @NewDrive + substring(@DefaultBackupDirectory,2,2 )
+ @RootDirectory2Add
+ substring(@DefaultBackupDirectory,charindex(@DefaultBackupDirectory,':') + 3, datalength(@DefaultBackupDirectory))
end
select @DefaultBackupDirectory = replace(@DefaultBackupDirectory, '\\','\')
declare @DOSCmd nvarchar(4000)
select @DOSCmd = N'if not exist "' + @DefaultBackupDirectory + N'" md "' + @DefaultBackupDirectory + N'"'
--print @DOSCmd
exec master..xp_cmdshell @DOSCmd, no_output
print '-- New Default Backup Directory'
print @DefaultBackupDirectory
exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', REG_SZ, @DefaultBackupDirectory
end
else
begin
print ' '
print '-- Standard Default Backup Directory has already been modified, use @Override=Y '
print @DefaultBackupDirectory
print '-- Default Backup Directory NOT altered !'
end
-- disable cmdshell if enabled in the previous part
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'0'
RECONFIGURE WITH OVERRIDE
end
--script : DBA Install sp_DBA_Create_BuDevices.sql
USE master
go
IF OBJECT_ID('sp_DBA_Create_BuDevices') IS NOT NULL
DROP PROCEDURE sp_DBA_Create_BuDevices
GO
CREATE PROCEDURE sp_DBA_Create_BuDevices
@DbName SYSNAME,
@AllDB char(1) = 'N'
AS
BEGIN
--backupdevices-installatie script
-- alzdba 30/03/2000
-- alzdba 23/04/2008 - added filegroup devices support
-- alzdba 24/04/2008 - converted to sp
--**************************
/* execution
DECLARE @rc int
DECLARE @DbName sysname
-- TODO: Set parameter values here.
Select @DbName = 'admin'
EXECUTE @rc = sp_DBA_Create_BuDevices
@DbName = @DbName
Print @rc
*/
IF upper(@AllDB) <> 'Y'
BEGIN
-- @DbName must be provided
IF @DbName IS NULL
RETURN ( -1 )
IF NOT EXISTS (select 1 from sys.databases where name = @DbName )
BEGIN
PRINT 'Database [' + @DbName + '] does not exist at server ' + @@servername
RETURN (-1)
END
END
SET nocount ON
-- interesseert ons niet wat draait
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Max_Aan_Dev INT
DECLARE @DriveLetter CHAR(1)
DECLARE @IncLogBackup CHAR(1)
DECLARE @Filepath VARCHAR(512)
DECLARE @FilepathLog VARCHAR(512)
DECLARE @FilegroupBackups CHAR(1)
--
--** onderstaande varriabelen hun waarde aanpassen !!! ******************************
SELECT @DriveLetter = '', -- leave '' to use default driveletter
@IncLogBackup = 'Y',
@FilegroupBackups = 'Y' -- set to Y if you want BUdevices for filegroups
--** bovenstaande varriabelen hun waarde aanpassen !!! ******************************
-- aantal aan te passen volgens nood (default = 3) (full/diff/log > 3 = ook met filegroups)
SELECT @Max_Aan_Dev = CASE WHEN @FilegroupBackups = 'Y' THEN 4
ELSE 3
END
-- cmdshell activeren indien nog niet actief
EXEC sys.sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE
DECLARE @CmdShellSettingBefore TABLE
(
cfgname VARCHAR(128),
minimum VARCHAR(128),
maximum VARCHAR(128),
config_value VARCHAR(128),
runvalue VARCHAR(128)
)
INSERT INTO @CmdShellSettingBefore
EXEC sys.sp_configure N'xp_cmdshell'
IF EXISTS ( SELECT *
FROM @CmdShellSettingBefore
WHERE cfgname = 'xp_cmdshell'
AND runvalue = '0' )
BEGIN
EXEC sys.sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
END
-- Default-path opvragen
DECLARE @DefaultBackupDirectory NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory',
@DefaultBackupDirectory OUTPUT
-- print @DefaultBackupDirectory
IF @DriveLetter = ''
SET @DriveLetter = SUBSTRING(@DefaultBackupDirectory, 1, 1)
-- path eventueel aanvullen (staat op de standaard ingesteld
-- set @Filepath = ':\MSSQL\BACKUP\'
SELECT @Filepath = SUBSTRING(CAST(@DefaultBackupDirectory AS VARCHAR(512)),
2, DATALENGTH(@DefaultBackupDirectory))
+ '\'
--if charindex('\',@@servername,1) > 1
--begin
-- set @Filepath = ':\MSSQL$' + substring(@@servername,charindex('\',@@servername,1)+1,128) + '\BACKUP\'
--end
--
IF @IncLogBackup = 'Y'
BEGIN
SELECT @FilepathLog = @Filepath + 'IncLogBackup\'
END
ELSE
BEGIN
SELECT @FilepathLog = @Filepath
END
Declare @tmpBuPaths table (BackupPath nvarchar(250) not null, LogBackupPath nvarchar(250) not null)
--print @Filepath + ' - ' + @FilepathLog
/******************************************************************************************************
** Check to see if a directories exists
******************************************************************************************************/
DECLARE @chkfile INT
DECLARE @exists BIT
DECLARE @ChkPath VARCHAR(5000)
DECLARE @DOSCmd VARCHAR(5000)
SELECT @ChkPath = @DriveLetter + @Filepath + '\*.*'
BEGIN
SELECT @DOSCmd = 'if not exist "' + @DriveLetter + @Filepath
+ '" md "' + @DriveLetter + @Filepath + '"'
EXEC master..xp_cmdshell @DOSCmd, no_output
END
SELECT @ChkPath = @DriveLetter + @FilepathLog + '\*.*'
BEGIN
SELECT @DOSCmd = 'if not exist "' + @DriveLetter + @FilepathLog
+ '" md "' + @DriveLetter + @FilepathLog + '"'
EXEC master..xp_cmdshell @DOSCmd, no_output
END
Insert into @tmpBuPaths values (@DriveLetter + @Filepath , @DriveLetter + @FilepathLog )
-- cmdshell terug afzetten indien ze af stond
IF EXISTS ( SELECT *
FROM @CmdShellSettingBefore
WHERE cfgname = 'xp_cmdshell'
AND runvalue = '0' )
BEGIN
EXEC sys.sp_configure N'xp_cmdshell', N'0'
RECONFIGURE WITH OVERRIDE
END
--********************************
DECLARE @teller INT
DECLARE @RETURNCode INT
SELECT @RETURNCode = 0
DECLARE @ServerNaam VARCHAR(30)
DECLARE @BuDevNaam SYSNAME
DECLARE @FileNaam VARCHAR(250)
DECLARE @Aan_Dev INT
SET @ServerNaam = @@servername
PRINT 'Install of Backup-devices for db ['+ @DbName +'] at server ' + @@servername
DECLARE @Dbteller INT
DECLARE @MaxDbAantal INT
SET @MaxDbAantal = 3
DECLARE @#DbNaam VARCHAR(128)
Declare @tmpBuDevices table (DbName sysname not null, BuDeviceName sysname not null, BuFileName nvarchar(250) not null )
-- cursor toevoegen en full en diffbu en logbu steps toevoegen
DECLARE DB_Csr CURSOR Local Fast_Forward
FOR SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( 'tempdb', 'model', 'Northwind', 'Pubs'
,'AdventureWorks','AdventureWorksDW','AdventureWorksLT'
,'Spotlight' )
AND name NOT LIKE '%[_]EUC'
AND ( name = @DbName
OR UPPER(@AllDB) = 'Y' )
ORDER BY name
FOR READ ONLY
OPEN DB_Csr
/*open the cursor*/
FETCH NEXT FROM DB_Csr INTO @#DbNaam
/*Get the 1st row*/
WHILE @@fetch_status = 0 /*set into loop until no more data can be found*/
BEGIN
IF @@fetch_status = 0
BEGIN
IF @FilegroupBackups = 'Y'
BEGIN
-- Get db filegroup inventory
DECLARE @PartitionedDbName SYSNAME
SET @PartitionedDbName = @#DbNaam
IF OBJECT_ID('tempdb..#T_DbFilegroups') IS NULL
BEGIN
CREATE TABLE #T_DbFilegroups
(
DBName SYSNAME NOT NULL,
FGname SYSNAME NOT NULL,
data_space_id INT NOT NULL,
[type] CHAR(2) COLLATE Latin1_General_CI_AS_KS_WS
NOT NULL,
type_desc NVARCHAR(60) COLLATE Latin1_General_CI_AS_KS_WS
NULL,
is_default BIT NULL,
filegroup_guid UNIQUEIDENTIFIER NULL,
log_filegroup_id INT NULL,
is_read_only BIT NULL
)
END
ELSE
BEGIN
TRUNCATE TABLE #T_DbFilegroups
END
DECLARE @SQLcmd NVARCHAR(MAX)
SET @SQLcmd = 'Insert into #T_DbFilegroups Select '''
+ @PartitionedDbName + ''', * from '
+ @PartitionedDbName + '.sys.filegroups ;'
EXEC sp_executesql @SQLcmd
END
--opvullen werkdefinities
IF @#DbNaam = 'Master'
BEGIN
SET @Aan_Dev = 1 -- Full
END
ELSE
IF @#DbNaam = 'Msdb'
BEGIN
SET @Aan_Dev = 2 -- Full / Diff
END
ELSE
BEGIN
SET @Aan_Dev = @Max_Aan_Dev -- zoals ingesteld bovenaan dit script
END
-- kreatie backup devices
SET @teller = 1
WHILE @teller <= @Aan_Dev
BEGIN
IF @teller = 4
BEGIN
IF EXISTS ( SELECT 1
FROM #T_DbFilegroups A
WHERE A.FGname <> 'PRIMARY' )
BEGIN
PRINT 'Creating filegroup devices for [' + @#DbNaam + '] **'
/* select all filegroups */
DECLARE csrDbFg CURSOR
FOR SELECT @#DbNaam + 'FG' + REPLICATE('0', ABS(3 - DATALENGTH(CONVERT(VARCHAR(15), data_space_id)))) + CONVERT(VARCHAR(15), data_space_id)
FROM #T_DbFilegroups
ORDER BY data_space_id ;
OPEN csrDbFg
FETCH NEXT FROM csrDbFg INTO @BuDevNaam
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FileNaam = @DriveLetter + @Filepath + @BuDevNaam + '.BAK'
EXECUTE @RETURNCode = sp_addumpdevice 'DISK',
@BuDevNaam,
@FileNaam
IF ( @@ERROR <> 0
OR @RETURNCode <> 0
)
BEGIN
PRINT '**Device ' + @BuDevNaam + ' already exists -> no action taken **'
END
ELSE
BEGIN
Insert into @tmpBuDevices values ( @#DbNaam, @BuDevNaam, @FileNaam)
END
FETCH NEXT FROM
csrDbFg INTO @BuDevNaam
END
CLOSE csrDbFg
DEALLOCATE csrDbFg
END
END
ELSE
BEGIN
SET @BuDevNaam = @#DbNaam
+ CASE @teller
WHEN 1 THEN 'Full'
WHEN 2 THEN 'Diff'
WHEN 3 THEN 'Log'
ELSE 'WRONG'
END
SELECT @FileNaam = CASE @teller
WHEN 3 THEN @DriveLetter + @FilepathLog + @BuDevNaam + '.BAK'
ELSE @DriveLetter + @Filepath + @BuDevNaam + '.BAK'
END
EXECUTE @RETURNCode = sp_addumpdevice 'DISK',
@BuDevNaam, @FileNaam
IF ( @@ERROR <> 0
OR @RETURNCode <> 0
)
BEGIN
PRINT '**Device ' + @BuDevNaam + ' already exists -> no action taken **'
END
ELSE
BEGIN
Insert into @tmpBuDevices values ( @#DbNaam, @BuDevNaam, @FileNaam)
END
END
SET @teller = @teller + 1
END
-- read next of cursor
FETCH NEXT FROM DB_Csr INTO @#DbNaam /* get the next row*/
END
END
-- close and cleanup cursor
CLOSE DB_Csr
DEALLOCATE DB_Csr
PRINT '** End of install backup devices for db [' + @DbName + ']**'
Select * from @tmpBuPaths
/* return only created devices */
Select *
from @tmpBuDevices
order by DbName, BuDeviceName
IF OBJECT_ID('tempdb..#T_DbFilegroups') IS NOT NULL
DROP TABLE #T_DbFilegroups
RETURN 0
END
go
Want to move backup DEVICE locations ?
use master
Declare @DevNameSuffix varchar(10)
Declare @OldPath varchar(500)
Declare @NewPath varchar(500)
select @DevNameSuffix = 'Log'
, @OldPath = 'T:\MSSQL\BACKUP\'
, @NewPath = 'T:\MSSQL\BACKUP\IncLogBackup\'
create table #TmpDevices(
device_name sysname ,
physical_name nvarchar(100),
description nvarchar(255),
status int ,
cntrltype smallint ,
size int )
declare @Tsql varchar(128)
declare @TsqlDrop nvarchar(500)
declare @TsqlAdd nvarchar(500)
set @Tsql = 'sp_helpdevice'
insert into #TmpDevices
exec (@Tsql)
declare csrDevices cursor for
select 'sp_dropdevice @logicalname = ''' + device_name + ''''
, 'sp_addumpdevice @devtype = ''disk'', @logicalname = ''' + device_name + ''', @physicalname = '''
+ replace(physical_name,@OldPath,@NewPath) + ''''
from #TmpDevices
where device_name like '%' + @DevNameSuffix
and cntrltype = 2
and physical_name like @OldPath + '%'
for read only
open csrDevices
FETCH NEXT FROM csrDevices
INTO @TsqlDrop, @TsqlAdd
WHILE @@FETCH_STATUS = 0
BEGIN
--T:\MSSQL\BACKUP --begin tran ReplLogDev --> The procedure 'sp_dropdevice' cannot be executed within a transaction.
exec ( @TsqlDrop )
exec ( @TsqlAdd )
--commit tran ReplLogDev
FETCH NEXT FROM csrDevices
INTO @TsqlDrop, @TsqlAdd
END
-- Cursor afsluiten
CLOSE csrDevices
DEALLOCATE csrDevices
--show new situation
create table #TmpDevicesNew(
device_name sysname ,
physical_name nvarchar(100),
description nvarchar(255),
status int ,
cntrltype smallint ,
size int )
set @Tsql = 'sp_helpdevice'
insert into #TmpDevicesNew
exec (@Tsql)
select 'xcopy "' + O.physical_name + '" "' + isnull(N.physical_name,'UNKNOWN') + '"' as TODO_DosCopy
from #TmpDevices O
Left join #TmpDevicesNew N
on O.device_name = N.device_name
where O.device_name like '%' + @DevNameSuffix
and O.physical_name like @OldPath + '%'
--and N.physical_name like @NewPath + '%'
order by O.device_name
drop table #TmpDevices
drop table #TmpDevicesNew
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply