03.11.07

Changing a property for all articles on a publication.

Posted in DBA, Microsoft Sql server, TSQL at 1:30 pm by

This week I had to change the pre_creation_cmd from ‘delete’ to ‘drop’ on all articles of a publication. I could do this manually or I could find all articles by using sp_helparticle. Lazy as I’m I opt for the latest option.

DECLARE @articles table
(
[article id] int,
[article name] varchar(255),
[base object] varchar(255),
[destination object] varchar(255),
[synchronization object] varchar(255),
[type] int,
status varchar(255),
filter varchar(255),
description varchar(255),
insert_command varchar(255),
update_command varchar(255),
delete_command varchar(255),
[creation script path] varchar(255),
[vertical partition] varchar(255),
pre_creation_cmd varchar(255),
filter_clause varchar(255),
schema_option varchar(255),
dest_owner varchar(255),
source_owner varchar(255),
unqua_source_object varchar(255),
sync_object_owner varchar(255),
unqualified_sync_object varchar(255),
filter_owner varchar(255),
unqua_filter varchar(255),
auto_identity_range varchar(255),
publisher_identity_range varchar(255),
identity_range varchar(255),
threshold varchar(255),
identityrangemanagementoption varchar(255),
fire_triggers_on_snapshot varchar(255)

);
DECLARE @article_name varchar(255);

INSERT INTO @articles
exec sp_helparticle  'Your replication publication name';

DECLARE article_cursor CURSOR FOR
SELECT [article name] as base_object FROM @articles;

OPEN article_cursor;

FETCH NEXT FROM article_cursor
INTO @article_name;

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC sp_changearticle
@publication = 'Your replication publication name',
@article = @article_name,
@property = N'pre_creation_cmd',
@value = 'drop',
@force_invalidate_snapshot = 1,
@force_reinit_subscription= 1;

FETCH NEXT FROM article_cursor
INTO @article_name;

END

CLOSE article_cursor;
DEALLOCATE article_cursor;
GO

After this you need to reinit all the subscriptions.

07.29.06

Restoring a database in sql server 2005

Posted in DBA, Microsoft Sql server at 2:12 am by

Sometimes you have to restore a backup file for a database with replication. This is how you do it.
Preparing the restore

  1. Copy the backed up database file to your database server. Then disconnect everyone from the database.
  2. In the windows start menu go to run and fill in: Services.msc and Press ok
  3. Find the Distributed Transaction Coordinator (msdtc.exe) and stop this service. (you do this by right clicking on it and selecting stop)
  4. Stop the sql server agent service

Restoring the database

  1. In Microsoft sql server management studio you connect to the server which holds the database you want to restore. Then you open the node databases and right click Restore/Database…In the Restore database window, you see a section Source for restore. There you select from device and you browse for the back up file you have on your server. After selecting it you will see a new line in the grid below. You select the checkbox restore.
  2. On the left side of the window you select the Options page.
  3. Select the option Overwrite the existing database & Preserve the replication settings.
  4. As recover state you leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored(Restore with recovery)

During the restore
Just wait will the back up is being restored.

After the restore
When the restore is finished you can turn back on the services you stopped.

Next entries »