Vba helper

Создание базы.

USE master

GO

IF NOT EXISTS (

SELECT name

FROM sys.databases

WHERE name = N'TutorialDB'

)

CREATE DATABASE [TutorialDB]

GO

ALTER DATABASE [TutorialDB] SET SQUERY_STORE=ON

GO

 

Создание таблицы.

USE TutorialDB

GO

-- Create a new table called 'Customers' in schema 'dbo'

-- Drop the table if it already exists

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL

DROP TABLE dbo.Customers

GO

-- Create the table in the specified schema

CREATE TABLE dbo.Customers

(

CustomerId        INT    NOT NULL   PRIMARY KEY, -- primary key column

Name      [NVARCHAR](50)  NOT NULL,

Location  [NVARCHAR](50)  NOT NULL,

Email     [NVARCHAR](50)  NOT NULL

);

Инструкция Transact-SQL не может располагаться на той же строке, что и команда GO. Тем не менее строка с командой GO может содержать комментарии.
Область видимости локальных (пользовательских) переменных ограничена пакетом, и к ним нельзя обращаться после команды GO.

Создание моментального снимка БД

CREATE DATABASE AdventureWorks_dbss1800 ON

( NAME = AdventureWorks_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )

AS SNAPSHOT OF AdventureWorks;

GO

 

--Creating sales_snapshot1200 as snapshot of the

--Sales database:

CREATE DATABASE sales_snapshot1200 ON

( NAME = SPri1_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SPri1dat_1200.ss'),

( NAME = SPri2_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SPri2dt_1200.ss'),

( NAME = SGrp1Fi1_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\mssql\data\SG1Fi1dt_1200.ss'),

( NAME = SGrp1Fi2_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SG1Fi2dt_1200.ss'),

( NAME = SGrp2Fi1_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SG2Fi1dt_1200.ss'),

( NAME = SGrp2Fi2_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SG2Fi2dt_1200.ss')

AS SNAPSHOT OF Sales;

GO

USE master;

-- Reverting AdventureWorks to AdventureWorks_dbss1800

RESTORE DATABASE AdventureWorks from

DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';

GO

DROP DATABASE SalesSnapshot0600 ;

 

--Test to see if sales_snapshot0600 exists and if it

-- does, delete it.

IF EXISTS (SELECT dbid FROM sys.databases

WHERE NAME='sales_snapshot0600')

DROP DATABASE SalesSnapshot0600;

GO

-- Reverting Sales to sales_snapshot1200

USE master;

RESTORE DATABASE Sales FROM DATABASE_SNAPSHOT = 'sales_snapshot1200';

GO