49 lines
1.5 KiB
Transact-SQL
49 lines
1.5 KiB
Transact-SQL
-- MS SQL Server Database Setup for Weather Connect
|
|
-- Run this as a user with CREATE DATABASE privileges (e.g., sa or sysadmin role)
|
|
-- This script creates the database and sets it up for use with Always On Availability Groups
|
|
|
|
-- 1. Check current server and database status
|
|
SELECT @@SERVERNAME AS ServerName, @@VERSION AS SQLVersion;
|
|
GO
|
|
|
|
-- 2. Create the database
|
|
-- Note: For Always On Availability Groups, the database must be created with FULL recovery model
|
|
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'WeatherDB')
|
|
BEGIN
|
|
CREATE DATABASE WeatherDB;
|
|
PRINT 'Database WeatherDB created successfully.';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
PRINT 'Database WeatherDB already exists.';
|
|
END
|
|
GO
|
|
|
|
-- 3. Set recovery model to FULL (required for Always On AG)
|
|
ALTER DATABASE WeatherDB SET RECOVERY FULL;
|
|
GO
|
|
|
|
-- 4. Verify database creation and settings
|
|
SELECT
|
|
name AS DatabaseName,
|
|
recovery_model_desc AS RecoveryModel,
|
|
state_desc AS State,
|
|
compatibility_level AS CompatibilityLevel
|
|
FROM sys.databases
|
|
WHERE name = 'WeatherDB';
|
|
GO
|
|
|
|
-- 5. Take a full backup (required before adding to Always On AG)
|
|
-- Modify the backup path as needed for your environment
|
|
BACKUP DATABASE WeatherDB
|
|
TO DISK = 'WeatherDB_Full.bak'
|
|
WITH FORMAT, INIT, NAME = 'WeatherDB Full Backup';
|
|
GO
|
|
|
|
PRINT 'Database WeatherDB is ready for use.';
|
|
PRINT 'If you plan to add this database to an Always On Availability Group:';
|
|
PRINT ' 1. Ensure full backup has been taken (done above)';
|
|
PRINT ' 2. Take a transaction log backup';
|
|
PRINT ' 3. Add the database to your AG using the AG wizard or T-SQL';
|
|
GO
|