Ошибка 3202 sql

While attempting to restore a 4 TB MDF/data database with a 1.8 TB LDF/log file, I’m receiving the following error:

Write on ‘???’ failed: 1117 (The request could not be performed because of an I/O device error.
Error: 3202, Severity: 16, State: 1

The backup appears to have created successfully on the primary machine, but will not restore to the backup machine. Both servers are running the SQL Server 2012 Enterprise Edition. The location of the .LDF transaction log file is being moved during the restore from the primary’s I drive to the backups L drive, but this never posed an issue in the past.

The database is made up of 3 .MDF files: one approximately 2 TB, another at 1.4 TB, and another around 300 GB. The .LDF transaction log file is about 1.8TB. The restore locations have enough space, with the drive for the .mdf files having 6 TB total and the drive for .ldf having 2 TB total.

Any thoughts would be greatly appreciated.

While attempting to restore a 4 TB MDF/data database with a 1.8 TB LDF/log file, I’m receiving the following error:

Write on '???' failed: 1117 (The request could not be performed because of an I/O device error.
Error: 3202, Severity: 16, State: 1

The backup appears to have created successfully on the primary machine, but will not restore to the backup machine. Both servers are running the SQL Server 2012 Enterprise Edition. The location of the .LDF transaction log file is being moved during the restore from the primary’s I drive to the backups L drive, but this never posed an issue in the past.

The database is made up of 3 .MDF files: one approximately 2 TB, another at 1.4 TB, and another around 300 GB. The .LDF transaction log file is about 1.8TB. The restore locations have enough space, with the drive for the .mdf files having 6 TB total and the drive for .ldf having 2 TB total.

Any thoughts would be greatly appreciated.

FYI: This article is also posted in StackOverflow at:
https://stackoverflow.com/questions/27116806/sql-server-restore-error-3202
I’m uncertain which location is best for this type of question.

C

How to migrate windows login in from one sql server to another sql server on another domain
So whatever logins the database has in DMN1 cannot be used in DMN2
anymore. it would take a lot of time to sort out a lot of windows
logins in DMN1, get their equivalent login in DMN2 and map them to the
right databases. What would be an easier way of doing it

Take a look at the two below scripts I’ve used for similar needs in the past. You will need to run these on each DB you need to mirror permissions for the two different domain accounts.
You will need to search the results of script #1 for the principal you need to mirror the access which is changing domains, etc. Once you get the logic out you need, run it with the new value of the domain credential from the new domain and then run that logic on each DB.
Use the script #2 query before and/or after if you wish per each DB to check the records for the security principals.

Script 1
/* Script DB Level Permissions v2.1 Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/

DECLARE
@sql VARCHAR(2048)
,@sort INT

DECLARE tmp CURSOR FOR

/*********************************************/ /********* DB CONTEXT STATEMENT *********/ /*********************************************/ SELECT ‘— [— DB CONTEXT —] —‘ AS [— SQL STATEMENTS —],
1 AS [— RESULT ORDER HOLDER —] UNION SELECT ‘USE’ + SPACE(1) + QUOTENAME(DB_NAME()) AS [— SQL STATEMENTS —],
1 AS [— RESULT ORDER HOLDER —]

UNION

SELECT » AS [— SQL STATEMENTS —],
2 AS [— RESULT ORDER HOLDER —]

UNION

/*********************************************/ /********* DB USER CREATION *********/ /*********************************************/

SELECT ‘— [— DB USERS —] —‘ AS [— SQL STATEMENTS —],
3 AS [— RESULT ORDER HOLDER —] UNION SELECT ‘IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ‘ + SPACE(1) + »» + [name] + »» + ‘) BEGIN CREATE USER ‘ + SPACE(1) + QUOTENAME([name]) + ‘ FOR LOGIN ‘ + QUOTENAME([name]) + ‘ WITH DEFAULT_SCHEMA = ‘ + QUOTENAME([default_schema_name]) + SPACE(1) + ‘END; ‘ AS [— SQL STATEMENTS —],
4 AS [— RESULT ORDER HOLDER —] FROM sys.database_principals AS rm WHERE [type] IN (‘U’, ‘S’, ‘G’) — windows users, sql users, windows groups

UNION

/*********************************************/ /********* DB ROLE PERMISSIONS *********/ /*********************************************/ SELECT ‘— [— DB ROLES —] —‘ AS [— SQL STATEMENTS —],
5 AS [— RESULT ORDER HOLDER —] UNION SELECT ‘EXEC sp_addrolemember @rolename =’
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), »») + ‘, @membername =’ + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), »») AS [— SQL STATEMENTS —],
6 AS [— RESULT ORDER HOLDER —] FROM sys.database_role_members AS rm WHERE USER_NAME(rm.member_principal_id) IN (
—get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 — 0 to 4 are system users/schemas
and [type] IN (‘G’, ‘S’, ‘U’) — S = SQL user, U = Windows user, G = Windows group
)
—ORDER BY rm.role_principal_id ASC

UNION

SELECT » AS [— SQL STATEMENTS —],
7 AS [— RESULT ORDER HOLDER —]

UNION

/*********************************************/ /********* OBJECT LEVEL PERMISSIONS *********/ /*********************************************/ SELECT ‘— [— OBJECT LEVEL PERMISSIONS —] —‘ AS [— SQL STATEMENTS —],
8 AS [— RESULT ORDER HOLDER —] UNION SELECT CASE
WHEN perm.state <> ‘W’ THEN perm.state_desc
ELSE ‘GRANT’
END
+ SPACE(1) + perm.permission_name + SPACE(1) + ‘ON ‘ + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ‘.’ + QUOTENAME(obj.name)
—select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE ‘(‘ + QUOTENAME(cl.name) + ‘)’
END
+ SPACE(1) + ‘TO’ + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> ‘W’ THEN SPACE(0)
ELSE SPACE(1) + ‘WITH GRANT OPTION’
END
AS [— SQL STATEMENTS —],
9 AS [— RESULT ORDER HOLDER —] FROM
sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
—WHERE usr.name = @OldUser
—ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT » AS [— SQL STATEMENTS —],
10 AS [— RESULT ORDER HOLDER —]

UNION

/*********************************************/ /********* DB LEVEL PERMISSIONS *********/ /*********************************************/ SELECT ‘— [—DB LEVEL PERMISSIONS —] —‘ AS [— SQL STATEMENTS —],
11 AS [— RESULT ORDER HOLDER —] UNION SELECT CASE
WHEN perm.state <> ‘W’ THEN perm.state_desc —W=Grant With Grant Option
ELSE ‘GRANT’
END
+ SPACE(1) + perm.permission_name —CONNECT, etc
+ SPACE(1) + ‘TO’ + SPACE(1) + ‘[‘ + USER_NAME(usr.principal_id) + ‘]’ COLLATE database_default —TO
+ CASE
WHEN perm.state <> ‘W’ THEN SPACE(0)
ELSE SPACE(1) + ‘WITH GRANT OPTION’
END
AS [— SQL STATEMENTS —],
12 AS [— RESULT ORDER HOLDER —] FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
—WHERE usr.name = @OldUser

WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 4 — 0 to 4 are system users/schemas
AND [usr].[type] IN (‘G’, ‘S’, ‘U’) — S = SQL user, U = Windows user, G = Windows group

UNION

SELECT » AS [— SQL STATEMENTS —],
13 AS [— RESULT ORDER HOLDER —]

UNION

SELECT ‘— [—DB LEVEL SCHEMA PERMISSIONS —] —‘ AS [— SQL STATEMENTS —],
14 AS [— RESULT ORDER HOLDER —] UNION SELECT CASE
WHEN perm.state <> ‘W’ THEN perm.state_desc —W=Grant With Grant Option
ELSE ‘GRANT’
END
+ SPACE(1) + perm.permission_name —CONNECT, etc
+ SPACE(1) + ‘ON’ + SPACE(1) + class_desc + ‘::’ COLLATE database_default —TO
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + ‘TO’ + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> ‘W’ THEN SPACE(0)
ELSE SPACE(1) + ‘WITH GRANT OPTION’
END
AS [— SQL STATEMENTS —],
15 AS [— RESULT ORDER HOLDER —] from sys.database_permissions AS perm
inner join sys.schemas s
on perm.major_id = s.schema_id
inner join sys.database_principals dbprin
on perm.grantee_principal_id = dbprin.principal_id WHERE class = 3 —class 3 = schema

ORDER BY [— RESULT ORDER HOLDER —]

OPEN tmp FETCH NEXT FROM tmp INTO @sql, @sort WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @sql
FETCH NEXT FROM tmp INTO @sql, @sort END

CLOSE tmp DEALLOCATE tmp

source
Script2
CREATE VIEW vwObjectPermissions
AS
SELECT schema_name(o.schema_id) AS [Schema_Name]
,o.NAME AS [object_name]
,u.NAME AS [principal_name]
,u.type_desc AS [principal_type]
,r.minor_id
,r.permission_name
,r.state_desc
,o.schema_id
,o.principal_id AS [alt_owner]
,o.type_desc
FROM sys.database_permissions r
LEFT JOIN sys.database_Principals u ON r.grantee_principal_id = u.principal_id
LEFT JOIN sys.all_objects o ON o.object_id = r.major_id
WHERE class_desc NOT IN (‘database’)
GO

—1. Check if Public or guest is granted any permission on an object (database role and server role)
SELECT *
FROM vwObjectPermissions
WHERE principal_name IN (‘Public’,’Guest’)

—2. Check if any user is granted permissions on an object rather than roles.
SELECT *
FROM vwObjectPermissions
WHERE principal_type NOT LIKE ‘%ROLE%’

—3. Check if a user has «with grant» previliges on an object
SELECT *
FROM vwObjectPermissions
WHERE state_desc = ‘WITH GRANT’ —check the spelling on this one

—4. Check who has access to extended stored procedures (which I get from select name from sysobjects where xtype=’X’)
SELECT *
FROM vwObjectPermissions
WHERE type_desc LIKE ‘%X%Proc%’
GO

DROP VIEW vwObjectPermissions;

source