Sql server экзамен

``sql
create database shop
on primary
( name = N'shop', filename = N'D:\DB\shop.mdf', size = 5MB , FILEGROWTH = 1MB, MaxSize = 10MB )
log on
( name = N'shop_log', filename = N'D:\DB\shop_log.ldf' , size = 3MB , FILEGROWTH = 1MB, MaxSize = 5MB );
go
use shop
GO
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name='shop'; -- проверка
select DB_NAME(database_id) [Database Name]
, type_desc [Database Name]
, name [Logical file Name]
, physical_name [Physical file Name]
, size*8/1024 [File Size (MB)]
, growth*8/1024 [File Growth (MB)]
from sys.master_files where database_id=DB_ID('shop');
GO
SELECT * into [shop].[dbo].Orders FROM [DEVFOXBD].[dbo].Orders --Копирование таблицы
SELECT * into [shop].[dbo].Products FROM [DEVFOXBD].[dbo].Products --Копирование таблицы
select * from Orders
select * from Products
GO
CREATE PROCEDURE DelMaxMin -- Процедура на удаление максимального и минимального значения
AS
Begin
DELETE FROM Products
Where Price IN (SELECT Max(Price) from Products) or Price IN (SELECT Min(Price) from Products)
END;
GO
EXEC DelMaxMin

CREATE PROCEDURE Price100 -- Процедура на увелечение
AS
Begin
UPDATE Products
SET Price = Price + 100
Where Price = 8000
END;
GO
EXEC Price100
GO
Create View View_Shop AS -- Вьюшка на параметры которые равны столько то
Select ID, Name, Price, Type, provider_ID
From Products
Where Price = 8100 or Price = 13000
Select * from View_Shop
Go


Create Role RoleShop -- создание роли

CREATE LOGIN Shop1 WITH PASSWORD='Shop234'
CREATE LOGIN Shop2 WITH PASSWORD='Shop235'

--Создание пользователей на основе учетных записей
CREATE USER Shop1 FOR LOGIN Shop1
CREATE USER Shop2 FOR LOGIN Shop2

ALTER ROLE RoleShop
ADD MEMBER Shop1;

ALTER ROLE RoleShop
ADD MEMBER Shop2;

GRANT EXECUTE ON DelMaxMIn TO RoleShop -- дать запуск процедуры делмахсмин для рольшоп
GRANT SELECT, UPDATE ON Orders To Shop2

select * From sys.database_role_members
select * From sys.database_permissions
select * From sys.database_principals

sp_helprolemember
sp_helprotect NULL, 'Shop2';
sp_helpuser Shop1

EXEC sp_addumpdevice 'disk','Nwc','C:\Backup\Nwc.bak'

-- Полное резервное копирование
BACKUP DATABASE [DEVFOXBD] to Nwc

-- Дифференциальное резервное копирование
BACKUP DATABASE DEVFOXBD TO Nwc WITH DIFFERENTIAL;
GO

-- Резервное копирование журнала транзакций
BACKUP LOG DEVFOXBD TO Nwc;
GO

sp_helpdb Shop

ttps://disk.yandex.ru/d/_9w26rLHA2hm1w

.