0] Set Simple Recovery mode - loop on every databases
DECLARE @name VARCHAR(50) --
database name
declare @query varchar(max);
DECLARE db_cursor CURSOR FOR
SELECT name FROM
master.dbo.sysdatabases WHERE name NOT
IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM
db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @query ='ALTER
DATABASE ' +
@name + '
SET RECOVERY SIMPLE ;'
print @query
exec(@query)
FETCH NEXT FROM db_cursor
INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
1] Backup
1.1 Activate Backup Compression at
Server level
sp_configure 'backup
compression default' ,
1
go
reconfigure
go
sp_configure 'backup
compression default'
And set backup-compression default server configuration option in
Maintenance Plan.
Backup Compression factor ranges from 5 to 10.
1.2 Remove old Backup files
-- Purge
old backup files « .bak » < date given in argument
-- Manual
date
EXECUTE master.dbo.xp_delete_file 0,N'\\w-issc-3\NAMED4',N'bak',N'2011-08-13T20:00:34',1
-- Or
calculated, exemple 5 days old
DECLARE @FiveDaysOld VARCHAR(50)
Set @FiveDaysOld=CAST(DATEADD(d, -5,
GETDATE()) AS VARCHAR)
-- Select
@FiveDaysOld
EXECUTE master.dbo.xp_delete_file 0,N'\\w-issc-3\NAMED4',N'bak',@FiveDaysOld,1
-- delete
backups from msdb.dbo.backupset tables
-- example
: when backups have been deleted with OS commands
USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = '18/12/2013';
Under build
1.3 Query last backup information msdb.dbo.backupset , msdb.dbo.backupmediafamily
-- every successful backup date & size list
-- for one database
SELECT database_name [Database],
CASE WHEN type
= 'D' THEN
'DB' WHEN type
= 'L' THEN
'Log' WHEN type
= 'I' THEN
'Diff' END as
Type,
physical_device_name, backup_start_date, backup_finish_date,
ceiling(backup_size/1024.0/1024.0) AS
BackupSize_MB ,
ceiling(compressed_backup_size/1024.0/1024.0) AS
Compressed_BackupSize_MB --
SQL 2008 only
FROM msdb.dbo.backupset
b JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'NPSXML'
ORDER BY backup_finish_date
DESC
Output
|
Database
|
Type
|
Backup
File
|
Start
Date
|
Finish
Date
|
Backup
Sz MB |
Compressed
Backup Sz MB |
|
NPSXML
|
Log
|
INST\NPSXML_backup_2011_09_09_112214_5370683.bak
|
2011-09-09
11:22:14.000
|
2011-09-09
11:23:31.000
|
454
|
89
|
|
NPSXML
|
DB
|
INST\NPSXML_backup_2011_09_08_211114_7362483.bak
|
2011-09-08
21:11:36.000
|
2011-09-08
21:19:30.000
|
1894
|
175
|
|
NPSXML
|
DB
|
INST\NPSXML_backup_2011_09_07_210857_0329889.bak
|
2011-09-07
21:09:10.000
|
2011-09-07
21:14:21.000
|
1874
|
172
|
|
NPSXML
|
DB
|
INST\NPSXML_backup_2011_09_06_210808_7592916.bak
|
2011-09-06
21:08:46.000
|
2011-09-06
21:16:33.000
|
1853
|
170
|
|
NPSXML
|
DB
|
INST\NPSXML_backup_2011_09_05_210509_7503334.bak
|
2011-09-05
21:05:22.000
|
2011-09-05
21:11:51.000
|
1839
|
168
|
|
NPSXML
|
DB
|
INST\NPSXML_backup_2011_09_02_210408_8121011.bak
|
2011-09-02
21:04:23.000
|
2011-09-02
21:12:30.000
|
1807
|
165
|
-- list every database successful backup in the past 24 hours
SELECT database_name [Database],
CASE WHEN type
= 'D' THEN
'DB' WHEN type
= 'L' THEN
'Log' WHEN type
= 'I' THEN
'Diff' END as
Type,
physical_device_name, backup_start_date, backup_finish_date,
ceiling(backup_size/1024.0/1024.0) AS
BackupSize_MB ,
ceiling(compressed_backup_size/1024.0/1024.0) AS
Compressed_BackupSize_MB --
SQL 2008 only
FROM msdb.dbo.backupset
b JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE (CONVERT(datetime, backup_start_date, 102)
>= GETDATE() - 1)
and database_name != 'model'
ORDER BY database_name
, backup_finish_date DESC
Output
|
Database
|
Type
|
Backup
File
|
Start
Date
|
Finish
Date
|
Backup
Sz MB |
Compressed
Backup Sz MB |
|
master
|
DB
|
INST\master_backup_2011_09_08_211114_7206481.bak
|
2011-09-08
21:11:15.000
|
2011-09-08 21:11:16.000
|
3
|
1
|
|
msdb
|
DB
|
INST\msdb_backup_2011_09_08_211114_7362483.bak
|
2011-09-08
21:11:22.000
|
2011-09-08
21:11:31.000
|
14
|
2
|
|
NPSXML
|
Log
|
INST\NPSXML_backup_2011_09_09_120001_5893327.bak
|
2011-09-09
12:00:01.000
|
2011-09-09
12:00:02.000
|
1
|
1
|
|
NPSXML
|
Log
|
INST\NPSXML_backup_2011_09_09_112214_5370683.trn
|
2011-09-09
11:22:14.000
|
2011-09-09
11:23:31.000
|
454
|
89
|
|
NPSXML
|
DB
|
INST\NPSXML_backup_2011_09_08_211114_7362483.bak
|
2011-09-08
21:11:36.000
|
2011-09-08
21:19:30.000
|
1894
|
175
|
1.4 Execute Backup on every
Databases
MS Original
BACKUP DATABASE [master] TO
DISK = N'\\w-inst-1\INST\master_backup_2011_09_12_090737_6572137.bak'
WITH NOFORMAT, NOINIT,
NAME = N'master_backup_2011_09_12_090737_6552135', SKIP, REWIND,
NOUNLOAD, STATS = 10
GO
sql server find backup filepath
sql server regular expression to find the left part of
string
Exemple Backup
select top
1 w.physical_device_name
FROM msdb.dbo.backupmediafamily
w where w.physical_device_name like '%trn'
filepath = \\SRV-1\backup\SRV_20140728152949.trn
select top 1 reverse(substring(reverse(w.physical_device_name),patindex('%\%',reverse(w.physical_device_name)),len(w.physical_device_name))) FROM
msdb.dbo.backupmediafamily w where
w.physical_device_name
like '%trn'
result \\SRV-1\backup\
This script finds the Backup Path from existing dumps and
creates dumps named <DB>_backup_YYYY_MM_DD_hhmmss
DECLARE @name VARCHAR(64) --
database name
DECLARE @path VARCHAR(255) --
path for backup files
DECLARE @fileName VARCHAR(255) -- filename for backup
DECLARE @fileDate VARCHAR(24) -- used for file name
-- SET
@path = 'C:\Backup\' -- SET @path = '\\w-inst-1\INST\'
set @path = (select top 1 reverse(substring(reverse(w.physical_device_name),patindex('%\%',reverse(w.physical_device_name)),len(w.physical_device_name))) FROM
msdb.dbo.backupmediafamily w )
--if
substring(@path,15,15) != '\'
--select
@path = @path + '\'
SELECT @fileDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(),
120), '-','_') ,':','') ,' ','_')
DECLARE db_cursor CURSOR FOR
SELECT name FROM
master.dbo.sysdatabases WHERE name NOT
IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM
db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path
+ @name + '_backup_'
+ @fileDate + '.bak'
BACKUP DATABASE @name TO DISK
= @fileName
FETCH NEXT FROM db_cursor
INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Output
Processed 280824 pages for database 'UsageAndHealth', file
'UsageAndHealth' on file 1.
Processed 39 pages for database 'UsageAndHealth', file
'UsageAndHealth_log' on file 1.
BACKUP DATABASE successfully processed 280863 pages in
33.453 seconds (65.591 MB/sec).
Same script for LOG BACKUP :
BACKUP DATABASE @name TO DISK
= @fileName
ð
BACKUP LOG @name
TO DISK =
@fileName
Msg 4208, Level 16, State 1, Line 19
The statement BACKUP LOG is not allowed while
the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model
using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 19
BACKUP LOG is terminating abnormally.
1.5 Log Backup for DB in FULL
Recovery Model
BACKUP LOG [O13]
TO
DISK = N'\\w-inst-1\NAMED3\O13_backup_2012_09_18_143131_9006392.bak'
WITH NOFORMAT, NOINIT,
NAME = N'O13_backup_2012_09_18_143131_8746392', SKIP,
REWIND, NOUNLOAD, STATS
= 10
15 pour cent traites.
25 pour cent traites.
61 pour cent traites.
88 pour cent traites.
100 pour cent traites.
352 pages traitees pour la base de
donnees 'O13', fichier 'O13_log' dans le fichier 1.
BACKUP LOG a traite avec succes 352
pages en 0.435 secondes (6.312 Mo/s).
2] Restore – at the Users DB Level
2.1 Run a FULL Backup ( exampe : O13 DB )
DECLARE @name VARCHAR(50) --
database name
DECLARE @path VARCHAR(256) --
path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
set @path = ( select top 1 substring(w.physical_device_name,1,15) Backup_Path FROM msdb.dbo.backupmediafamily w )
if substring(@path,15,15)
!= '\'
select @path = @path
+ '\'
SELECT @fileDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), GETDATE(),
120), '-','_') ,':','') ,' ','_')
set @name='O13'
SET @fileName = @path
+ @name + '_backup_'
+ @fileDate + '.bak'
BACKUP DATABASE @name TO DISK
= @fileName
- Query
last backup information, see 1.3
|
DB
|
type
|
physical_device
|
start
|
finish
|
size_MB
|
compress
size_MB |
|
O13
|
DB
|
_2012_02_14_184013.bak
|
2012-02-14
18:40:13.000
|
2012-02-14
18:40:14.000
|
2
|
1
|
- Check
backup file
RESTORE HEADERONLY from disk
= '\\w-inst-1\NAMED3\O13_backup_2012_02_14_184013.bak'
restore filelistonly
from disk =
'\\w-inst-1\NAMED3\O13_backup_2012_02_14_184013.bak'
|
Logical_Name
|
Physical_Name
|
Type
|
FileGroupName
|
Size
|
MaxSize
|
|
O13
|
V:\MSSQL10_50.NAMED3\MSSQL\DATA\O13.mdf
|
D
|
PRIMARY
|
2359296
|
3,51844E+13
|
|
O13_log
|
V:\MSSQL10_50.NAMED3\MSSQL\DATA\O13_log.LDF
|
L
|
NULL
|
589824
|
2,19902E+12
|
2.2 Deconnect Users , then Restore with no recovery
Restore brings back DB at the Time Of The Backup.
alter database O13 set
single_user with rollback immediate
restore database O13
from disk =
'\\w-inst-1\NAMED3\O13_backup_2012_02_14_184013.bak'
alter database O13 set
multi_user
english !!
Les transactions non qualifiees sont annulees. Fin de l'operation
(estimation) : 100%.
Msg 3159, Niveau 16, Etat 1, Ligne 3
La fin du journal pour la base de donnees "O13" n'a pas ete
sauvegardee. Utilisez BACKUP LOG WITH NORECOVERY pour sauvegarder le journal
s'il contient des travaux que vous ne voulez pas perdre. Utilisez la clause
WITH REPLACE ou WITH STOPAT de l'instruction RESTORE pour remplacer simplement
le contenu du journal.
Msg 3013, Niveau 16, Etat 1, Ligne 3
RESTORE DATABASE s'est termine anormalement.
Use the WITH REPLACE clause when DB is in FULL Recovery Mode
or we want to create a new DB
alter database O13 set
single_user with rollback immediate
restore database O13
from disk =
'\\w-inst-1\NAMED3\O13_backup_2012_02_14_184013.bak'
with replace
alter database O13 set
multi_user
168 pages traitees pour la base de
donnees 'O13', fichier 'O13' dans le fichier 1.
6 pages traitees pour la base de
donnees 'O13', fichier 'O13_log' dans le fichier 1.
RESTORE DATABASE a traite avec succes
174 pages en 0.244 secondes (5.555 Mo/s).
SQL Server 2008 R2
Restore normally prevents accidentally overwriting a database with a
different database. If the database specified in a RESTORE statement already
exists on the current server and the specified database family GUID differs
from the database family GUID recorded in the backup set, the database is not
restored. This is an important safeguard.
|
Caution
|
|
REPLACE should be used rarely and only after careful consideration.
|
The REPLACE option overrides several important safety checks that
restore normally performs. The overridden checks are as follows:
Restoring over an existing database with a backup taken of another
database.
With the REPLACE option, restore allows you to overwrite an existing
database with whatever database is in the backup set, even if the specified database
name differs from the database name recorded in the backup set. This can result
in accidentally overwriting a database by a different database.
Restoring over a database using the full or bulk-logged recovery model
where a tail-log backup has not been taken and the STOPAT option is not used.
With the REPLACE option, you can lose committed work, because the log
written most recently has not been backed up.
Overwriting existing files.
For example, a mistake could allow overwriting files of the wrong type,
such as .xls files, or that are being used by another database that is not
online. Arbitrary data loss is possible if existing files are overwritten,
although the restored database is complete.
2.3 Restore UNTIL TIME
This time, Logs will be applied to restore Point In Time.
- Check
for Recovery Model : needs to be FULL
select name
, database_id, recovery_model_desc
Recovery from sys.databases
- Let’s
do some Inserts in the O13..
SQL_DGC_V_COLLEGE_2 table
use O13
select * from
SQL_DGC_V_COLLEGE_2
insert SQL_DGC_V_COLLEGE_2 values ( 20 ,
30 )
select GETDATE() --
2012-09-18 11:49:31.920
insert SQL_DGC_V_COLLEGE_2 values ( 99 ,
99 )
select * from
SQL_DGC_V_COLLEGE_2
A B
10 20
20 30
99 99
We will restore before the last insert ( see getdate above ).
- First do a Log
backup , then purge Log
dbcc sqlperf('logspace') -- 40 % used
BACKUP LOG [O13]
TO
DISK = N'\\w-inst-1\NAMED3\O13_backup_2012_09_18_143131_9006392.bak'
WITH NOFORMAT, NOINIT,
NAME = N'O13_backup_2012_09_18_143131_8746392', SKIP,
REWIND, NOUNLOAD, STATS
= 10
dbcc sqlperf('logspace') -- 9 % used
Available dumps
|
DB
|
Type
|
Phys_name_device
|
backup_start
|
backup_end
|
Bup_Sz_MB
|
Compress_Sz_MB
|
|
O13
|
Log
|
\\w-inst-1\NAMED3\O13_backup_2012_09_18_143131_9006392.bak
|
2012-09-18
14:33:19.000
|
2012-09-18 14:33:19.000
|
3
|
1
|
|
O13
|
DB
|
\\w-inst-1\NAMED3\O13_backup_2012_09_17_220018_4323988.bak
|
2012-09-17
22:00:29.000
|
2012-09-17
22:00:29.000
|
3
|
1
|
- Restore the full
backup. Leave database in restoring state
USE [master]
GO
RESTORE DATABASE [O13]
FROM DISK =
N'\\w-inst-1\NAMED3\O13_backup_2012_09_17_220018_4323988.bak'
WITH FILE =
1, NORECOVERY, STATS
= 10
GO
69 percent processed.
100 percent processed.
Processed 184 pages for database
'O13', file 'O13' on file 1.
Processed 1 pages for database 'O13',
file 'O13_log' on file 1.
RESTORE DATABASE successfully
processed 185 pages in 0.411 seconds (3.516 MB/sec).
If we select :
Msg 927, Niveau 14,
État 2, Ligne 1
Database 'O13' cannot be opened. It
is in the middle of a restore.
--STEP 2: Completely restore 1st log backup. Leave database
in restoring state
-- RESTORE LOG [O13]
-- FROM DISK =
N'C:\SQLBackups\Chapter5\DatabaseForLogBackups_Native_Log.trn'
-- WITH FILE = 1, NORECOVERY, STATS = 10
-- GO
- P-I-T restore of
2nd log backup. Recover the database
RESTORE LOG [O13]
FROM DISK =
N'\\w-inst-1\NAMED3\O13_backup_2012_09_18_143131_9006392.bak'
WITH FILE =
1, NOUNLOAD, STATS
= 10,
STOPAT = N'September
18, 2012 11:49:31 AM' ,
-- configure your time here
RECOVERY
GO
15 percent processed.
25 percent processed.
61 percent processed.
88 percent processed.
100 percent processed.
Processed 0 pages for database 'O13',
file 'O13' on file 1.
Processed 352 pages for database
'O13', file 'O13_log' on file 1.
RESTORE LOG successfully processed
352 pages in 0.334 seconds (8.221 MB/sec).
Check result : OK
select * from
SQL_DGC_V_COLLEGE_2
A B
10 20
20 30
2.4 Restore with data+log filepaths change
restore database test1
from disk = 'E:\pas-sauvegarde\bdi.nak'
with move 'BDI_PRODUCTION_Data' to 'E:\pas-sauvegarde\BDI_PRODUCTION.mdf',
move 'BDI_PRODUCTION_Log' to 'E:\pas-sauvegarde\BDI_PRODUCTION_1.ldf',
stats=10
2.5 Restore Database to a New Name ( and new filepaths
)
Find logical name and restore with MOVE :
RESTORE FILELISTONLY FROM DISK='E:\IPL_Data\SQL\Backup\<DB_1>_backup_2013_12_17_211134_3332615.bak'
>LogicalName Physical Name
>--------------
><DB_1>_Data E:\IPL_Data\SQL\MSSQL10_50.IPL\MSSQL\DATA\<DB_1>_Data.mdf
><DB_1>_Log E:\IPL_Data\SQL\MSSQL10_50.IPL\MSSQL\DATA\<DB_1>_log.ldf
RESTORE DATABASE <DB_1>_171213 FROM DISK='E:\IPL_Data\SQL\Backup\<DB_1>_backup_2013_12_17_211134_3332615.bak'
WITH
MOVE '<DB_1>_Data' TO 'E:\IPL_Data\SQL\MSSQL10_50.IPL\MSSQL\DATA\<DB_1>_171213_Data.mdf',
MOVE
'<DB_1>_Log' TO 'E:\IPL_Data\SQL\MSSQL10_50.IPL\MSSQL\DATA\<DB_1>_171213_log.ldf'
Processed 4728 pages for database '<DB_1>_171213',
file '<DB_1>_Data' on file 1.
Processed 2 pages for database '<DB_1>_171213',
file '<DB_1>_Log' on file 1.
RESTORE DATABASE successfully
processed 4730 pages in 0.285 seconds (129.639 MB/sec).
restore HEADERONLY
from disk =
'F:\Backup\SQL_Server\W-INST-1\APP\<DB>_backup_2014_11_13_220425_5452320.bak'
restore filelistonly
from disk =
'F:\Backup\SQL_Server\W-INST-1\APP\<DB>_backup_2014_11_13_220425_5452320.bak'
alter database
<DB> set single_user with rollback
immediate
restore database
<DB> from disk =
'F:\Backup\SQL_Server\W-INST-1\APP\<DB>_backup_2014_11_13_220425_5452320.bak'
with
move 'BASE_DATA_Data'
to 'E:\INST_Data\Program Files\Microsoft SQL Server\MSSQL11.INST\MSSQL\DATA\<DB>.mdf',
move 'BASE_DATA_Log'
to 'E:\INST_Data\Program Files\Microsoft SQL Server\MSSQL11.INST\MSSQL\DATA\<DB>_log.ldf',
replace
alter database
<DB> set multi_user
2.6 Restore Database to New Filepaths and Upgrade from
2008R2 to 2012
-
I installed first SQL Server 2012
Engine,
-
OPTIONAL : Then create
DB in this engine
-
Now, I restore Backup from SQL 2008R2 as
follow :
alter database BASE_CONF04 set single_user
with rollback immediate
restore database BASE_CONF04 from disk
= 'E:\DATA_IPL\Microsoft SQL Server\MSSQL11.IPL\MSSQL\Backup\BASE_CONF04_backup_2014_02_04_213023_0676491.bak'
with move 'DB_Data'
to 'E:\DATA_IPL\Microsoft SQL Server\MSSQL11.IPL\MSSQL\DATA\BASE_CONF04_Data.mdf',
move 'DB_Log' to 'E:\DATA_IPL\Microsoft
SQL Server\MSSQL11.IPL\MSSQL\DATA\BASE_CONF04_log.ldf', replace
alter database
BASE_CONF04 set multi_user
Processed 18784 pages for database 'BASE_CONF04',
file 'DB_Data' on file 1.
Processed 2 pages for database 'BASE_CONF04',
file 'DB_Log' on file 1.
Converting database 'BASE_CONF04'
from version 661 to the current version 706.
Database 'BASE_CONF04' running the
upgrade step from version 661 to version 668.
Database 'BASE_CONF04' running the
upgrade step from version 668 to version 669.
…
Database 'BASE_CONF04' running the
upgrade step from version 705 to version 706.
RESTORE DATABASE successfully
processed 18786 pages in 2.769 seconds (53.002 MB/sec).
RESTORE HEADERONLY
from disk =
'F:\BACKUP_DEMO_CONFIG_2013_175.bak'
restore filelistonly
from disk =
'F:\BACKUP_DEMO_CONFIG_2013_175.bak'
C:\databases\DB01_Data.mdf
C:\databases\DB01_log.ldf
alter database
DB01 set single_user
with rollback immediate
restore database
DB01
from disk
= 'F:\BACKUP_DEMO_CONFIG_2013_175.bak'
WITH REPLACE,
move 'DB01'
to 'E:\DATA_IPL\Microsoft SQL Server\MSSQL11.IPL\MSSQL\DATA\DB01_Data.mdf',
move 'DB01_log'
to 'E:\DATA_IPL\Microsoft SQL Server\MSSQL11.IPL\MSSQL\DATA\DB01_log.ldf',
stats=10
alter database DB01 set
multi_user
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 26784 pages for database 'DB01',
file 'DB01' on file 1.
Processed 2 pages for database 'DB01',
file 'DB01_log' on file 1.
Converting database 'DB01' from
version 661 to the current version 706.
Database 'DB01' running the upgrade
step from version 661 to version 668.
Database 'DB01' running the upgrade
step from version 668 to version 669.
Database 'DB01' running the upgrade
step from version 669 to version 670.
…
Database 'DB01' running the upgrade
step from version 703 to version 704.
Database 'DB01' running the upgrade
step from version 704 to version 705.
Database 'DB01' running the upgrade
step from version 705 to version 706.
RESTORE DATABASE successfully
processed 26786 pages in 8.955 seconds (23.367 MB/sec).
2.7 Restore using files coming from a System backup (
snap or so … )
USE MASTER;
GO
-- Take database in single user mode -- if you are
facing errors
-- This may terminate your active transactions for
database
ALTER DATABASE
ODS SET SINGLE_USER
WITH ROLLBACK INSTEDIATE;
GO
-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'ODS'
GO
-- Restore MDF & LOG from SNAP
-- Re-Attached DB
CREATE DATABASE
[ODS] ON
( FILENAME
= N'P:\MSSQL10_50.GCTPP\MSSQL\Data\ODS_Datas.mdf'
),
( FILENAME
= N'P:\MSSQL10_50.GCTPP\MSSQL\Data\ODS_Log.mdf' )
FOR ATTACH
GO
2.8 Restore a non existing database will create it !
restore database
DATABASE2 from disk =
'<Path>\DATABASE1_backup_2015_06_22_220547_5925467.bak'
with move
'BASE_CONFIG_Data' to 'E:\DATA_IPL\Microsoft SQL Server\MSSQL11.IPL\MSSQL\DATA\DATABASE2_Data.mdf',
move 'BASE_CONFIG_Log'
to 'E:\DATA_IPL\Microsoft SQL Server\MSSQL11.IPL\MSSQL\DATA\DATABASE2_log.ldf', replace
alter database
DATABASE2 set multi_user
3] Restore – Instance Level [ Disaster Recovery ]
Restore from « scratch » of an Instance on a New
Windows Server, with Filepath Change.
DR Instance_Name = PCA
3.0 Pre-requisite
Restriction : source and DR SQL Servers must be on the
exact service pack
Get the Version of the Instance we are trying to
restore with SSMStudio :
RESTORE HEADERONLY
from disk =
'\\w-inst-1\NAMED\\master_backup_2012_09_06_200615_9982806.bak'
master_backup_2012_09_06_200615_9826805 NULL 1 NULL 0 1 2 DOMAIN\isscsvc W-INTANCE-1\NAMED 10 50 1600
3.1 Silent Install of a SQL Server Engine
Prepare Configuration file
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
INSTANCEID="PCA"
ACTION="Install"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT
HELP="False"
INDICATEPROGRESS="False"
QUIET="False"
QUIETSIMPLE="true"
X86="False"
ENU="False"
;UIMODE="Normal"
ERRORREPORTING="False"
INSTALLSHAREDDIR="D:\Program
Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="D:\Program Files
(x86)\Microsoft SQL Server"
INSTANCEDIR="D:\Program Files\Microsoft
SQL Server"
SQMREPORTING="False"
INSTANCENAME="PCA"
AGTSVCACCOUNT="DOMAIN\SERVERSVC"
AGTSVCSTARTUPTYPE="Manual"
ISSVCSTARTUPTYPE="Automatic"
ISSVCACCOUNT="NT
AUTHORITY\NetworkService"
ASSVCSTARTUPTYPE="Automatic"
ASCOLLATION="Latin1_General_CI_AS"
ASDATADIR="Data"
ASLOGDIR="Log"
ASBACKUPDIR="Backup"
ASTEMPDIR="Temp"
ASCONFIGDIR="Config"
ASPROVIDERMSOLAP="1"
FARMADMINPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="False"
SQLCOLLATION="French_CI_AS"
SQLSVCACCOUNT="DOMAIN\SERVERSVC"
SQLSYSADMINACCOUNTS="DOMAIN\Admins du
domaine" "BUILTIN\Administrateurs"
INSTALLSQLDATADIR="P:\"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Automatic"
RSSVCSTARTUPTYPE="Automatic"
RSINSTALLMODE="FilesOnlyMode"
FTSVCACCOUNT="NT AUTHORITY\LOCAL
SERVICE"
C:\> E:\Setup.exe
/ConfigurationFile="D:\Pas-sauvegarde\ConfigurationFile_InstanceSQLPCA.ini"
/IAcceptSQLServerLicenseTerms /SQLSVCPASSWORD="*******"
/AGTSVCPASSWORD="*******"
3.2 Restore Master DB
- Stop
the Sql Server Services
C:\scripts> Get-Service *pca*
Status
Name
DisplayName
------
----
-----------
Stopped
MSSQL$PCA SQL Server
(PCA)
Stopped
MSSQLFDLauncher... SQL Full-text Filter Daemon Launche...
Stopped
SQLAgent$PCA Agent SQL Server (PCA)
- Start
the Instance in Single User mode
"D:\Program
Files\Microsoft SQL Server\MSSQL10_50.PCA\MSSQL\Binn"
C:\
sqlservr.exe -m -s PCA
2012-09-12
08:25:00.69 Server Microsoft SQL Server 2008 R2
(RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service
Pack 1)
2012-09-12
08:25:00.70 Server (c) Microsoft Corporation.
2012-09-12
08:25:00.70 Server All rights reserved.
2012-09-12
08:25:00.70 Server Server process ID is 7772.
2012-09-12
08:25:00.70 Server System Manufacturer: 'HP',
System Model: 'ProLiant BL460c G7'.
2012-09-12
08:25:00.70 Server Authentication mode is WINDOWS-ONLY.
2012-09-12
08:25:00.70 Server Logging SQL Server messages in
file 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\Log\ERRORLOG'.
2012-09-12
08:25:00.70 Server This instance of SQL Server
last reported using a process ID of 3296 at 12/09/2012 08:23:38 (local)
12/09/2012 06:2
3:38 (UTC).
This is an informational message only; no user action is required.
2012-09-12
08:25:00.70 Server Registry startup parameters:
-d P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\master.mdf
-e P:\SQLDATA\MSSQL10_50.PCA\MSSQL\Log\ERRORLOG
-l P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\mastlog.ldf
2012-09-12
08:25:00.70 Server Command Line Startup
Parameters:
-m
-s PCA
PCA
2012-09-12
08:25:00.72 Serveur SQL Server is starting at normal
priority base (=7). This is an informational message only. No user action is
requi
red.
2012-09-12
08:25:00.72 Serveur Detected 24 CPUs. This is an
informational message; no user action is required.
2012-09-12
08:25:00.72 Server Cannot use Large Page
Extensions: lock memory privilege was not granted.
2012-09-12
08:25:01.16 Serveur Perfmon counters for resource
governor pools and groups failed to initialize and are disabled.
2012-09-12
08:25:01.19 Serveur Using dynamic lock allocation.
Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per
node. This i
s an
informational message only. No user action is required.
2012-09-12
08:25:01.20 Serveur Lock partitioning is enabled.
This is an informational message only. No user action is required.
2012-09-12
08:25:01.21 Serveur Node configuration: node 0: CPU
mask: 0x0000000000fff000:0 Active CPU mask: 0x0000000000fff000:0. This message
prov
ides a
description of the NUMA configuration for this computer. This is an
informational message only. No user action is required.
2012-09-12
08:25:01.21 Serveur Node configuration: node 1: CPU
mask: 0x0000000000000fff:0 Active CPU mask: 0x0000000000000fff:0. This message
prov
ides a
description of the NUMA configuration for this computer. This is an
informational message only. No user action is required.
2012-09-12
08:25:01.24 Serveur Database Mirroring Transport is
disabled in the endpoint configuration.
2012-09-12
08:25:01.25 spid9s Warning ******************
2012-09-12
08:25:01.25 spid9s SQL Server started in
single-user mode. This an informational message only. No user action is
required.
2012-09-12
08:25:01.25 spid9s Starting up database 'master'.
2012-09-12
08:25:01.69 spid9s Recovery is writing a
checkpoint in database 'master' (1). This is an informational message only. No
user action is
required.
2012-09-12
08:25:02.09 spid9s SQL Server Audit is starting
the audits. This is an informational message. No user action is required.
2012-09-12
08:25:02.09 spid9s SQL Server Audit has started
the audits. This is an informational message. No user action is required.
2012-09-12
08:25:02.12 spid9s FILESTREAM: effective level =
0, configured level = 0, file system access share name = 'PCA'.
2012-09-12
08:25:02.21 spid9s SQL Trace ID 1 was started by
login "sa".
2012-09-12
08:25:02.21 spid9s Starting up database
'mssqlsystemresource'.
2012-09-12
08:25:02.23 spid9s The resource database build
version is 10.50.1600. This is an informational message only. No user action is
require
d.
2012-09-12
08:25:02.89 spid11s Starting up database 'model'.
2012-09-12
08:25:02.92 spid9s Server name is 'W-SERVER-6\PCA'.
This is an informational message only. No user action is required.
2012-09-12
08:25:03.06 Serveur A self-generated certificate was
successfully loaded for encryption.
2012-09-12
08:25:03.06 Serveur Server is listening on [ 'any'
<ipv6> 50996].
2012-09-12
08:25:03.06 Serveur Server is listening on [ 'any'
<ipv4> 50996].
2012-09-12
08:25:03.06 Serveur Server local connection provider is
ready to accept connection on [ \\.\pipe\SQLLocal\PCA
].
2012-09-12
08:25:03.07 Serveur Server local connection provider is
ready to accept connection on [ \\.\pipe\MSSQL$PCA\sql\query
].
2012-09-12
08:25:03.07 Serveur Server is listening on [ ::1
<ipv6> 50997].
2012-09-12
08:25:03.07 Serveur Server is listening on [ 127.0.0.1
<ipv4> 50997].
2012-09-12
08:25:03.07 Serveur Dedicated admin connection support
was established for listening locally on port 50997.
2012-09-12
08:25:03.10 spid13s A new instance of the full-text
filter daemon host process has been successfully started.
2012-09-12
08:25:03.11 Serveur The SQL Server Network Interface
library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/W-SERVER
-6.domain.comp_name.fr:PCA
] for the SQL Server service.
2012-09-12
08:25:03.11 Serveur The SQL Server Network Interface
library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/W-SERVER
-6.domain.comp_name.fr:50996
] for the SQL Server service.
2012-09-12
08:25:03.11 Serveur SQL Server is now ready for client
connections. This is an informational message; no user action is required.
2012-09-12
08:25:03.12 spid9s Starting up database 'msdb'.
2012-09-12
08:25:03.38 spid11s Clearing tempdb database.
2012-09-12
08:25:05.08 spid11s Starting up database 'tempdb'.
2012-09-12
08:25:05.55 spid9s Recovery is complete. This is
an informational message only. No user action is required.
- Restore
DB
In another Command line :
C:\ sqlcmd -S ".\PCA"
1> RESTORE
DATABASE MASTER
2> FROM
disk = '\\w-inst-1\NAMED\\master_backup_2012_09_10_200656_4244162.bak'
3> WITH
REPLACE
4> GO
368 pages
traitées pour la base de données 'MASTER', fichier 'master' dans le fichier 1.
3 pages traitées
pour la base de données 'MASTER', fichier 'mastlog' dans le fichier 1.
Restauration
réussie de la base de données master. Arrêt du serveur SQL Server.
SQL Server met fin à ce processus.
- Start
the Instance without Filepath Checking of the MSDB , TEMPDB and MODEL DBs
D:\> net start MSSQL$PCA /f /T3608
Le
service SQL Server (PCA) démarre..
Le
service SQL Server (PCA) a démarré.
- Change
the Filepath of TEMPDB and MODEL
D:\>
sqlcmd -S ".\PCA"
1>
ALTER DATABASE model MODIFY FILE( NAME = modeldev , FILENAME = 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\model.mdf')
2>
GO
Le
fichier "modeldev" a été modifié dans le catalogue système. Le
nouveau chemin sera utilisé au prochain démarrage de la base de données.
1>
ALTER DATABASE model MODIFY FILE( NAME = modellog , FILENAME = 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\model.ldf')
2>
GO
Le
fichier "modellog" a été modifié dans le catalogue système. Le
nouveau chemin sera utilisé au prochain démarrage de la base de données.
1>
ALTER DATABASE tempdb MODIFY FILE( NAME = tempdev , FILENAME = 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\tempdb.mdf')
2>
Go
Le
fichier "tempdev" a été modifié dans le catalogue système. Le nouveau
chemin sera utilisé au prochain démarrage de la base de données.
1>
ALTER DATABASE tempdb MODIFY FILE( NAME = templog , FILENAME =
'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\tempdb.ldf')
2>
GO
Le fichier "templog" a été modifié dans le
catalogue système. Le nouveau chemin sera utilisé au prochain démarrage de la
base de données.
- Restart
the SQL Services and SQL Full Text to get the new Filepath
3.3 Restore MSDB DB
D:\> sqlcmd -S ".\PCA"
1> RESTORE
DATABASE msdb
2> FROM DISK
='\\w-inst-1\k$\NAMED\msdb_backup_2012_09_10_200656_4400163.bak'
3> WITH
REPLACE,
4> MOVE
'msdbdata' TO 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\MSDBData.mdf',
5> MOVE
'msdblog' TO 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\MSDBDlog.ldf'
6> GO
1688 pages
traitées pour la base de données 'msdb', fichier 'MSDBData' dans le fichier 1.
7 pages traitées
pour la base de données 'msdb', fichier 'MSDBLog' dans le fichier 1.
RESTORE DATABASE
a traité avec succès 1695 pages en 1.988 secondes (6.661 Mo/s).
- Start
SQL Server Agent
3.4 Restore Applicative DBs
Get DB Logical Names
RESTORE FILELISTONLY from disk = '\\w-inst-1\k$\NAMED\ETL_backup_2012_09_10_200656_4868166.bak'
D:\> sqlcmd -S ".\PCA"
1> RESTORE
DATABASE ETL
2> FROM DISK
='\\w-inst-1\k$\NAMED\ETL_backup_2012_09_10_200656_4868166.bak'
3> WITH
REPLACE,
4> MOVE
'etl_datas' TO 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\ETLData.mdf',
5> MOVE
'etl_log' TO 'P:\SQLDATA\MSSQL10_50.PCA\MSSQL\DATA\ETLlog.ldf'
6> GO
Restore time : 1.2 GB / 5 minutes
4] Login / User mismatch following a Restore
-- Map user DB01 to the DB DB01
( seen unmapped in Studio )
-- mismatch
Use master
SELECT sid FROM sys.syslogins WHERE name = 'DB01'
0x183A0F0453740B4A930AA41E578036D7
Use DB01
SELECT sid FROM dbo.sysusers WHERE name = 'DB01'
0x691009645600B941A0312B53C5EE60AF
-- report the mismatch
USE DB01
GO
EXEC sp_change_users_login 'Report'
GO
username user_sid
DB01 0x691009645600B941A0312B53C5EE60AF
APP_ADMIN 0xA7433E85442F464EB0614E600452168A
USE DB01
EXEC sp_change_users_login 'update_one', 'DB01', 'DB01'
EXEC sp_change_users_login 'update_one', 'APP_ADMIN', 'APP_ADMIN'
Use DB01
SELECT sid FROM dbo.sysusers WHERE name = 'DB01'
0x183A0F0453740B4A930AA41E578036D7
-- other check
Use master
SELECT sid FROM sys.syslogins WHERE name = 'US1'
0xC034948BED7C434EB837D4C13CD7EB8F
Use DB01
SELECT sid FROM dbo.sysusers WHERE name = 'US1'
0xC034948BED7C434EB837D4C13CD7EB8F
EXEC sys.sp_MSforeachdb 'USE ?;DECLARE @name VARCHAR(50)
SELECT @name=DB_NAME() ; if @name
like ''BASE%'' begin print @name
EXEC sp_change_users_login
''Report''
end'
5] Get a SUSPECT Database back in Business
SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE
STATE_DESC='SUSPECT'
GO
-- CONFIG inaccessible
EXEC sp_resetstatus [Config];
--
Warning: You must recover this database prior to access.
ALTER DATABASE [Config] SET EMERGENCY
DBCC checkdb([Config])
/*
DBCC
results for 'Config'.
Service
Broker Msg 9675, State 1: Message Types analyzed: 14.
Service
Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service
Broker Msg 9667, State 1: Services analyzed: 3.
Service
Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service
Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service
Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service
Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service
Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC
results for 'sys.sysrscols'.
There are
1305 rows in 16 pages for object "sys.sysrscols".
DBCC
results for 'sys.plan_persist_query_text'.
There are
0 rows in 0 pages for object "sys.plan_persist_query_text".
DBCC
results for 'sys.plan_persist_query'.
There are
0 rows in 0 pages for object "sys.plan_persist_query".
CHECKDB
found 0 allocation errors and 4 consistency errors in database 'Config'.
repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB (Config).
DBCC
execution completed. If DBCC printed error messages, contact your system administrator.
*/
ALTER DATABASE [Config] SET SINGLE_USER WITH ROLLBACK
IMMEDIATE
--Nonqualified
transactions are being rolled back. Estimated rollback completion: 0%.
--Nonqualified
transactions are being rolled back. Estimated rollback completion: 100%.
DBCC CheckDB ([Config], REPAIR_ALLOW_DATA_LOSS)
/*
Warning:
The log for database 'Config' has been rebuilt. Transactional consistency has
been lost. The RESTORE chain was broken, and the server no longer has context
on the previous log files, so you will need to know what they were.
You should
run DBCC CHECKDB to validate physical consistency. The database has been put in
dbo-only mode. When you are ready to make the database available for use, you
will need to reset database options and delete any extra log files.
Msg 924,
Level 14, State 1, Line 248
Database
'Config' is already open and can only have one user at a time.
DBCC
results for 'Config'.
CHECKDB
found 0 allocation errors and 0 consistency errors in database 'Config'.
*/
ALTER DATABASE [Config] SET MULTI_USER
