Creating a Database in Sybase: A Quick Guide

 

If you're coming from a Microsoft SQL Server (MSSQL) background, you'll notice that Sybase ASE handles things a little differently — especially when it comes to storage.

Key Differences from MSSQL

In Sybase, you must create devices before creating a database. These devices are physical files on disk and are used to store data and transaction logs. Typically, you'll create:

• A data device (e.g., datadev)

• A log device (e.g., logdev)

This separation allows for better performance and easier management.

---

Basic Syntax to Create a Database

Here’s a simple example that shows how to create a database in Sybase:

 

USE master
GO

-- Create the database using data and log devices
CREATE DATABASE mydatabase
ON datadev1 = 1000
LOG ON logdev1 = 250
GO

-- Extend the database with more space on another data device
ALTER DATABASE mydatabase
ON datadev2 = 1000
GO

-- Add even more space
ALTER DATABASE mydatabase
ON datadev2 = 348
GO

-- Set the database owner to 'sa' for full access
USE mydatabase
GO
EXEC sp_changedbowner 'sa'
GO 

Tips

• I usually name my devices datadev and logdev for clarity.

• Always consider setting the database owner to sa, especially if you want SQL Agent jobs or scripts to run with full rights.

• Be sure your devices are created ahead of time using the disk init command.