Archive for November, 2014

Setup Replication or Disaster Recovery Of MS SQL Express Edition Databases

MS SQL Express Edition does not have the feature of Log-shipping like the Standard or Enterprise Edition . So if you want to setup Replication for MS SQL Express  Edition you would need to use SQL Script to backup up databases to the remote server and then another SQL Script to restore the database on the remote server .

Here is One way which I have tried and it is working successfully  .  The Environment has two servers at remote location from each other , both the servers have MS SQL 2008 Express Edition installed on the Servers .  On the Primary Server I have used the following script to backup all databases of Primary Server on the secondary server to a Folder configured as a Network Share .

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
— DECLARE @fileDate VARCHAR(20) — used for file name
— specify database backup directory
SET @path = ‘\\Network_Share_folder\db_backup_folder\’

— specify filename format
— SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) — exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName WITH INIT
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Create a .bat file to Execute the SQL query and set it in task scheduler

sqlcmd -S INSTANCENAME  -i “C:\dbscripts\Backup_Databases.sql”

This Script will generate backups without time stamp and will overwrite the backup files every time it is executed .  Now the Question comes of restoring the databases on the secondary server .  You can use the below script to restore the databases on secondary Server

restore database DBNAME
from disk =’D:\DB_BACKUP\DBNAME.bak’
With REPLACE

restore database DBNAME1
from disk =’D:\DB_BACKUP\DBNAME1.bak’
With REPLACE

restore database DBNAME2
from disk =’D:\DB_BACKUP\DBNAME2.bak’
With REPLACE

Create a .bat file to Execute the SQL query and set it in task scheduler

So you are done with the Replication or Disaster Recovery  of MS SQL Databases with MS SQL Express Edition .

Happy Learning :)

MySQL Query to delete Multiple Pending Posts in WordPress

DELETE FROM `wp_posts` WHERE `post_status` LIKE ‘pending';

MySQl Query to Delete Unapproved Comments in WordPress

DELETE  * FROM wp_comments WHERE comment_approved = ‘0’

(adsbygoogle = window.adsbygoogle || []).push({}); //--> Feedjit Flag Counter