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
Comments