SQL Server cannot restart the server to find the following error in the error log:
Operating System Error 1117 (unable to run this request due to an I/O device error .) On file "C: \ Program Files \ microsoft sqlserver \ mssql10.mssqlserver \ MSSQL \ data \ mastlog. LDF"
Most of these problems are caused by disk hardware errors that make sqlserver unable to restart. If the file is not damaged, we can solve them through the mobile sqlserver system database. If the system file is damaged, You Need To rebuildsystem the database and restore the system database.
Here we will talk about how to move the system database to another disk to solve the problem of hardware problems that cause service failure to restart:
1. First copy the system database to another disk and make sure that the SQL Server service account has sufficient permissions to access these files.
2. In the Configuration Manager, modify the SQL server startup parameters and specify the master database files and log files to other directories.
-D indicates the location of the master data file.
-L indicates the location of the master log file.
3. After modification, run the following command to start SQL Server:
Start named SQL server instance:
Net startmssql $ InstanceName/f/t3608
Start default instance:
Net start MSSQLServer/f/t3608
4. Run the preceding command to start SQL Server, and then use sqlcmd to connect to the master database to modify the file path of other system databases. If you use management
Select "new query" directly when connecting to Studio (single-user mode is enabled, only one user is allowed to connect to the database, and "new query" connection can ensure that a single user is successfully connected ).
Command for modifying the system database file path:
Use master
Go
Alter databasetempdbmodifyfile
(Name = tempdev, filename
= 'D: \ Program Files \ microsoftsql Server \ mssql10_50.r2 \ MSSQL \ data \ backup \ tempdb. MDF ')
Go
Alter databasetempdbmodifyfile
(Name = templog, filename
= 'D: \ Program Files \ microsoftsql Server \ mssql10_50.r2 \ MSSQL \ data \ backup \ templog. ldf ')
Go
Use master;
Go
Alter databasemsdbmodifyfile
(Name = msdbdata, filename = 'd: \ Program Files \ microsoftsql Server \ mssql10_50.r2 \ MSSQL \ data \ backup \ msdbdata. MDF ');
Go
Alter databasemsdbmodifyfile
(Name = msdblog, filename = 'd: \ Program Files \ microsoftsql Server \ mssql10_50.r2 \ MSSQL \ data \ backup \ msdblog. ldf ');
Go
Alter databasemodelmodifyfile
(Name = modeldev, filename = 'd: \ Program Files \ microsoftsql Server \ mssql10_50.r2 \ MSSQL \ data \ backup \ model. MDF ');
Go
Alter databasemodelmodifyfile
(Name = modellog, filename = 'd: \ Program Files \ microsoftsql Server \ mssql10_50.r2 \ MSSQL \ data \ backup \ modellog. ldf ');
Go
Check whether the file address has been modified:
Select name, physical_nameas currentlocation, state_desc
From SYS. master_files
Where database_id = db_id (n' <database_name> ');
--- Use the following command to migrate the resource database
Use master;
Go
Alter database mssqlsystemresourcemodifyfile (name = data, filename = 'd: \ Program
Files \ microsoft sqlserver \ mssql10_50.r2 \ MSSQL \ data \ backup \. MDF ');
Go
Alter database mssqlsystemresourcemodifyfile (name =
Log, filename = 'd: \ Program Files \ microsoftsql Server \ mssql10_50.r2 \ MSSQL \ data \ backup \. ldf ');
Go
Alter database mssqlsystemresourcesetread_only;
5. After successful execution, restart the database to restore to normal. If other user databases also need to be migrated, you can refer to the above command to migrate files.
6. After the migration is complete, run DBCC checkdb to ensure database integrity and back up the database.
For more information about migration system databases, see: http://msdn.microsoft.com/en-us/library/ms345408.aspx