Restauration db master

Version imprimable

Bonjour,

J'ai essayé de tester la resto de la base master d'un server 2008R2:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
C:\Binn>sqlservr.exe -c -m -s INSTANCE3 -T3607
2015-10-02 15:07:04.72 Server      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
        Jun 17 2011 00:54:03
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
2015-10-02 15:07:04.72 Server      (c) Microsoft Corporation.
2015-10-02 15:07:04.72 Server      All rights reserved.
2015-10-02 15:07:04.72 Server      Server process ID is 1860.
2015-10-02 15:07:04.72 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2015-10-02 15:07:04.72 Server      Authentication mode is MIXED.
2015-10-02 15:07:04.72 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE3\MSSQL\Log\ERRORLOG'.
2015-10-02 15:07:04.73 Server      This instance of SQL Server last reported using a process ID of 3436 at 02/10/2015 15:06:44 (local) 02/10/2015 13:06:44 (UTC). This is an in
formational message only; no user action is required.
2015-10-02 15:07:04.73 Server      Registry startup parameters:
         -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE3\MSSQL\DATA\master.mdf
         -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE3\MSSQL\Log\ERRORLOG
         -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE3\MSSQL\DATA\mastlog.ldf
2015-10-02 15:07:04.73 Server      Command Line Startup Parameters:
         -c
         -m
         -s INSTANCE3
         INSTANCE3
         -T 3607
2015-10-02 15:07:04.73 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2015-10-02 15:07:04.74 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2015-10-02 15:07:04.77 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2015-10-02 15:07:04.77 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational messag
e only.  No user action is required.
2015-10-02 15:07:04.81 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the
 NUMA configuration for this computer. This is an informational message only. No user action is required.
2015-10-02 15:07:04.82 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2015-10-02 15:07:04.83 spid6s      Warning ******************
2015-10-02 15:07:04.83 spid6s      SQL Server started in single-user mode. This an informational message only. No user action is required.
2015-10-02 15:07:04.83 spid6s      Starting without recovery. This is an informational message only. No user action is required.
2015-10-02 15:07:04.84 spid6s      Starting up database 'master'.
2015-10-02 15:07:05.05 Server      A self-generated certificate was successfully loaded for encryption.
2015-10-02 15:07:05.06 Server      Server is listening on [ 'any' <ipv6> 54514].
2015-10-02 15:07:05.06 Server      Server is listening on [ 'any' <ipv4> 54514].
2015-10-02 15:07:05.06 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\INSTANCE3 ].
2015-10-02 15:07:05.06 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$INSTANCE3\sql\query ].
2015-10-02 15:07:05.06 Server      Server is listening on [ ::1 <ipv6> 54515].
2015-10-02 15:07:05.06 Server      Server is listening on [ 127.0.0.1 <ipv4> 54515].
2015-10-02 15:07:05.06 Server      Dedicated admin connection support was established for listening locally on port 54515.
2015-10-02 15:07:05.16 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x5, state:
4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required i
f Kerberos authentication is required by authentication policies.
2015-10-02 15:07:05.16 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.

Je reste donc bloqué à la 1ère étape alors qu'un premier test s'est déjà bien déroulé.
J'ai également essayé sans -T3607 ...

En se connectant avec un autre compte Windows mais qui est dans le groupe administrator, j'ai ce message:

Code:

1
2
3
4
5
6
7
8
9
10
11
C:\Binn>sqlservr.exe -m -s INSTANCE3
2015-10-02 14:18:23.39 Server      Error: 17058, Severity: 16, State: 1.
2015-10-02 14:18:23.39 Server      initerrlog: Could not open error log file ''. Operating system error = 3(failed to retrieve text for this error. Reason: 15100).
2015-10-02 14:18:23.69 Server      Error: 17058, Severity: 16, State: 1.
2015-10-02 14:18:23.69 Server      initerrlog: Could not open error log file ''. Operating system error = 3(failed to retrieve text for this error. Reason: 15105).
2015-10-02 14:18:23.99 Server      Error: 17058, Severity: 16, State: 1.
2015-10-02 14:18:23.99 Server      initerrlog: Could not open error log file ''. Operating system error = 3(failed to retrieve text for this error. Reason: 15105).
2015-10-02 14:18:24.29 Server      Error: 17058, Severity: 16, State: 1.
2015-10-02 14:18:24.29 Server      initerrlog: Could not open error log file ''. Operating system error = 3(failed to retrieve text for this error. Reason: 15105).
2015-10-02 14:18:24.59 Server      Error: 17058, Severity: 16, State: 1.
2015-10-02 14:18:26.10 Server      initerrlog: Could not open error log file ''. Operating system error = 3(failed to retrieve text for this error. Reason: 15105).

Sachant que j'ai control full sur tous les fichiers de server dont les LOG.

Merci.

@+

Bonjour,

Je cherche à savoir pour quoi la restauration master ne se déclenche pas ?

Code:

1
2
3
4
5
6
7
8
C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE3\MSSQL\Binn>NET START "SQL Server (INSTANCE3)" /f  /T3608
The SQL Server (INSTANCE3) service is starting.
The SQL Server (INSTANCE3) service was started successfully.
 
C:\MSSQL\Binn>sqlcmd -d master -S MonServer\instance3
1> RESTORE DATABASE master FROM DISK = 'G:\SQL-BACKUP\INSTANCE-3\master\master_backup_2015_10_01_184508_5192510.bak' WITH REPLACE;
2> go;
3>

Merci.

@+

Bonjour,

J'ai fait un test avec la base model:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
D:\>NET START "SQL Server (INSTANCE1)" /f /T3608
The SQL Server (INSTANCE1) service is starting.
The SQL Server (INSTANCE1) service was started successfully.
 
 
 
sqlcmd -d master -S MonServer\instance1 -i attache_model-1.sql -o attache_model.log
 
 
HResult 0x6D, Level 16, State 1
Shared Memory Provider: The pipe has been ended.
 
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link failure.

Merci.

21/10/2015, 14h26
Boubou2020

aucun ressemblance entre la procédure du restauration du base model avec celle du base master
chaqu'un a sa propre modèle du restauration
21/10/2015, 15h49
dari68

Citation:

Envoyé par abdallah_mehdoini

aucun ressemblance entre la procédure du restauration du base model avec celle du base master
chaqu'un a sa propre modèle du restauration

Bonjour,

C'était un autre test: j'ai détaché la base model et je voudrai la réattacher complétement indépendant de la resto Master.

Merci.

@+
22/10/2015, 11h16
mikedavem

1 pièce(s) jointe(s)

Hello,

Pour restaurer master, redémarrer sqlserver en mode mono utilisateur suffit. (> cf. documentation Microsoft ici)
Par exemple :

Pièce jointe 190769

Il ne reste plus qu'à redémarrer le service SQL Server de manière standard et le tour est joué

++
22/10/2015, 16h26
dari68

Merci bcp.

@+

Bonjour,

J'ai essayé de "patcher" une instance 2008R2SP1 en SP2 dont la base master a été restaurée dernièrrement:

Code:

1
2
3
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
 
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Je ne sais pas si l'erreur est liée à la restauration récente de la base Master ou pas ?

Merci.

@+

03/11/2015, 18h55
mikedavem

Hello,

Commence par vérifier la pertinence des valeurs de clés de registre suivantes (peut être à adapter avec ton environnement)

(HKEY_LOCAL_MACHINE\Sofware\Microsoft\Microsoft SQL Server\<instance>\MSSQLServer) - BackupDirectory, DefaultData et DefaultLog
(HKEY_LOCAL_MACHINE\Sofware\Microsoft\Microsoft SQL Server\<instance>\Setup) - SQLDataRoot

++