Tag: Database

Schedule Database Backup in SQL Server

1. In Microsoft SQL Server Management Studio, right-click SQL Server Agent, then click New, then click Job

2. Input Name, “JOB_BACKUP_DATABASE”

3. Click Steps, then click New

4. Input Step Name, “BACKUP DATABASE TO DISK”

5. Paste the following Command

DECLARE @todayText char(8)
SET @todayText = CONVERT(char(8), GETDATE(), 112)

DECLARE @path nvarchar(256)
SET @path = N'D:\DatabaseBackup\DB_NAME.{DATESTAMP}.bak'
SET @path = REPLACE(@path, '{DATESTAMP}', @todayText);

BACKUP DATABASE [DB_NAME]
    TO  DISK = @path
    WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME - Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

6. Click OK

7. Click Schedules, then click New

8. Input Name, “Daily 18:00″

9. Setup the appropriate schedule

10. Click OK

11. Click OK again

Backup SQL Server Database using Script

DECLARE @todayText char(8)
SET @todayText = CONVERT(char(8), GETDATE(), 112)

DECLARE @path nvarchar(256)
SET @path = N'D:\DatabaseBackup\DB_NAME.{DATESTAMP}.bak'
SET @path = REPLACE(@path, '{DATESTAMP}', @todayText);

BACKUP DATABASE [DB_NAME] 
	TO  DISK = @path
	WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME - Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore SQL Server Database

1. In SQL Server Management Studio, connect to the server as sa

2. Expand Databases

Microsoft SQL Server Management Studio
Microsoft SQL Server Management Studio

3. Right-click the database, point to Tasks, point to Restore, and then click Database…

Restore Database - General Page
Restore Database – General Page

4. In Restore Database dialog, verify the destination database name

5. On the General page, specify the source and location of backup sets to restore

Restore Database - Options Page
Restore Database – Options Page

6. On the Options page, select Overwrite the existing database (WITH REPLACE)

7. Click OK to start restore

Restore Completed
Restore Completed

References

Backup SQL Server Database

1. In SQL Server Management Studio, connect to the server as sa

2. Expand Databases

Microsoft SQL Server Management Studio
Microsoft SQL Server Management Studio

3. Right-click the database, point to Tasks, and then click Back Up…

Back Up Database
Back Up Database

4. In Back Up Database dialog, verify the database name

5. In the Backup type dropdown, select Full

6. Specify the backup destination

7. Click OK to start backup

Backup Completed
Backup Completed

References

Lesson Learned: GUID as a Primary Key

In RDBMS, it’s very common to use an int value as a Primary Key (PK). In SQL Server, we have Identity column. In Oracle, we have Sequence.

I’ve never thought of using GUID as a PK. Why we need to spend extra 12 bytes for a PK? (int is 4 bytes vs. GUID is 16 bytes)

In my recent project, the system consists of 2 kind of databases. 1) Server-side RDBMS (SQL Server), 2) Mobile-side RDBMS (Android SQLite). Initially, we designed to use int datatype as a PK for tables which need to be synchronized between the 2 database. However, this approach cannot uniquely identify the record across the 2 databases at all time.

We researched and found a solution to change the PK to GUID. GUID = Globally Unique Identifier. The GUID is so random that the probability of the same number being generated randomly twice is negligible.

This solution saved us 1) Extra roundtrip to query the newly generated Identity value from server RDBMS, 2) Extra roundtrip to synchronize the Identity value from server to the mobile.

How to: Using GUID in Database

Database Data Type Generation
SQL Server UNIQUEIDENTIFIER NEWID()
Oracle RAW(16) SYS_GUID()

References:

 

Schedule DBCC CHECKDB in SQL Server

1. In Microsoft SQL Server Management Studio, right-click SQL Server Agent, then click New, then click Job

2. Input Name, “JOB_DBCC_CHECKDB”

3. Click Steps, then click New

4. Input Step Name, “DBCC CHECKDB”

5. Paste the following Command

DECLARE @pDbName varchar(1000)

DECLARE c CURSOR FOR SELECT name FROM [sys].[databases] WHERE name <> 'tempdb'

OPEN c

FETCH NEXT FROM c INTO @pDbName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('DBCC CHECKDB(' + @pDbName + ');')

    FETCH NEXT FROM c INTO @pDbName
END

CLOSE c

DEALLOCATE c

6. Click OK

7. Click Schedules, then click New

8. Input Name, “Weekly Sunday 00:00”

9. Setup the appropriate schedule

10. Click OK

11. Click OK again

Reorganize / Rebuild Indexes in SQL Server

1. Check the fragmentation of an index

SELECT O.name, I.name, S.avg_fragmentation_in_percent
FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
    [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
    [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
WHERE S.avg_fragmentation_in_percent > 5 
ORDER BY S.avg_fragmentation_in_percent DESC;
avg_fragmentation_in_percent value Corrective statement
> 5% and <= 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD

2. Reorganize Index & Update Statistics

Reorganize index will not automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME} REORGANIZE;
UPDATE STATISTICS {OBJECT_NAME} {INDEX_NAME};

3. Rebuild Index

Rebuild index will automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME};

4. Everything in one shot

DECLARE @pSqlText varchar(1000)

DECLARE c CURSOR FOR SELECT SqlGenerator.SqlText FROM 
    (SELECT CASE WHEN S.avg_fragmentation_in_percent > 5 AND S.avg_fragmentation_in_percent <= 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REORGANIZE; UPDATE STATISTICS ' + O.name + ' ' + I.name + ';'
        WHEN S.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REBUILD;' ELSE '' END AS SqlText
    FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
        [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
        [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
    WHERE I.name IS NOT NULL) SqlGenerator
    WHERE SqlGenerator.SqlText <> ''

OPEN c

FETCH NEXT FROM c INTO @pSqlText

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@pSqlText)

    FETCH NEXT FROM c INTO @pSqlText
END

CLOSE c

DEALLOCATE c

References