Admin
15 апр. 2018 г.3 мин.
Пост обновлен 29 авг. 2021 г.
Backup
--Всей базы (backup)
BACKUP DATABASE [<name db>] TO [name backup device] WITH NOFORMAT, NOINIT, NAME = N"msSys - Full", SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
--Разностный (должен быть полный)
BACKUP DATABASE [<name base>] TO [name backup device] WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N"msSys-Разностная", SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
DBCC SHRINKFILE (N"<name log device>" , 0)
GO
--Backup log
BACKUP LOG [<name base>] TO [<name device>] WITH NOFORMAT, NOINIT, NAME = N"msSys-Журнал", SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
SHRINK
Для усечения файлов/БД используется встроенный пакет
DBCC_SHRINKFILE или DBCC_SHRINKDATABASE
DBCC SHRINKDATABASE:
( database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]
---------------------------------------------------------
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Активные сессии
select S.host_name,S.login_name,S.login_time,Q.*
From
(
select session_id as SPID,DB_NAME(database_id) as DB,
start_time,status,command,blocking_session_id,
wait_type,wait_time,last_wait_type,wait_resource,
cpu_time,reads,writes,logical_reads,row_count,query_hash
from sys.dm_exec_requests
where status<>'sleeping' and
status<>'background' and
query_hash<>0x43C643B5BD858345
)Q
inner join sys.dm_exec_sessions S
on Q.SPID=S.session_id
order by start_time
Восстановление БД до точки сбоя
1. Бэкап текущего журнала
backup log <database name> to <backup device> with norecovery, no_truncate;
2. Восстановите базу данных из ее полной
RESTORE DATABASE <database_name> FROM <backup_device> WITH NORECOVERY;
3. Примените все журналы транзакций
RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;
4. Восстановите базу данных
RESTORE DATABASE <database_name> WITH RECOVERY;
Копирование таблицы
Задача:
Перенести таблицу из БД tdb1 в базу tdb2 в рамках одного сервера.
Исходная таблица: tdb1.dbo.sprav_new
выполнить в редакторе запросов:
select * into tdb2.dbo.sprav_new from tdb1.dbo.sprav_new
Настройка сервера MS SQL
В свойствах сервера(максимальная конфигурация):
Память: по формуле - всего памяти - 4(8) ГБ для оси
Число рабочих потоков: не ставить 0, так как в этом случаи сервер не создаст потоков больше 255, чего может не хватить для корректной работы.
Поставить галку поддерживать приоритет сервера.
Проверка БД
use <name db>
go
dbcc checkdb
go
Создание БД
CREATE DATABASE [<name databe>]
CONTAINMENT = PARTIAL
ON PRIMARY
( NAME = N"<name databe>", FILENAME = N"C:\Program
Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL
\DATA\MyDB01.dbf" , SIZE = 5120KB , FILEGROWTH =
10%)
LOG ON
( NAME = N"<name databe>_log", FILENAME = N"C:
\Program Files\Microsoft SQL Server
\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDBLog01.dbf" ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [<name databe>] ADD FILEGROUP
[myrror]
GO
ALTER DATABASE [<name databe>] SET
COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [<name databe>] SET
ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [<name databe>] SET ANSI_NULLS OFF
GO
ALTER DATABASE [<name databe>] SET ANSI_PADDING
OFF
GO
ALTER DATABASE [<name databe>] SET ANSI_WARNINGS
OFF
GO
ALTER DATABASE [<name databe>] SET ARITHABORT
OFF
GO
ALTER DATABASE [<name databe>] SET AUTO_CLOSE
OFF
GO
ALTER DATABASE [<name databe>] SET
AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [<name databe>] SET AUTO_SHRINK
OFF
GO
ALTER DATABASE [<name databe>] SET
AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [<name databe>] SET
CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [<name databe>] SET
CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [<name databe>] SET
CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [<name databe>] SET
NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [<name databe>] SET
QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [<name databe>] SET
RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [<name databe>] SET
DISABLE_BROKER
GO
ALTER DATABASE [<name databe>] SET
AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [<name databe>] SET
DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [<name databe>] SET
PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [<name databe>] SET
READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [<name databe>] SET READ_WRITE
GO
ALTER DATABASE [<name databe>] SET RECOVERY FULL
GO
ALTER DATABASE [<name databe>] SET MULTI_USER
GO
ALTER DATABASE [<name databe>] SET PAGE_VERIFY
CHECKSUM
GO
ALTER DATABASE [<name databe>] SET
DEFAULT_FULLTEXT_LANGUAGE = 1049
GO
ALTER DATABASE [<name databe>] SET
DEFAULT_LANGUAGE = 1049
GO
ALTER DATABASE [<name databe>] SET
NESTED_TRIGGERS = ON
GO
ALTER DATABASE [<name databe>] SET
TRANSFORM_NOISE_WORDS = OFF
GO
ALTER DATABASE [<name databe>] SET
TWO_DIGIT_YEAR_CUTOFF = 2049
GO
ALTER DATABASE [<name databe>] SET
TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [<name databe>]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE
is_default=1 AND name = N"PRIMARY") ALTER DATABASE
[<name databe>] MODIFY FILEGROUP [PRIMARY]
DEFAULT
GO