Ads
19 June 2014
Adding Columns to Replicated Tables
sp_repladdcolumn @source_object =
'authors'
, @column = 'newcol'
, @typetext = 'INT'
, @publication_to_add = '
included in>'
Moving Database Files on the secondary server in Log Shipping
http://blogs.technet.com/b/mdegre/archive/2009/09/25/moving-database-files-on-the-secondary-server-in-log-shipping.aspx
You are using Microsoft SQL Server Logshipping for one of your VLDB
(very large database). You would like to move data files on another disk
on the secondary server without reconfiguring log shipping.
As you cannot use the detach/attach database, you could follow the way below :
This solution is inspired of the documentation "Moving Database Files" http://technet.microsoft.com/en-us/library/ms345483.aspx
Step 1 :
If your secondary is with the option "Standby Mode", you must change by
"no recovery mode". And perform a transaction (insert, delete or
update)
After, you have to manually run the job of backup, Then the jog of copy and the restore job.
Step 2 : Collect the logical name :
SELECT
name as logicalname, physical_name as filename
FROM
sys.master_files
WHERE
database_id = DB_ID('yourDatabaseName');
Step 3 : Move the file on the path expected :
ALTER DATABASE yourDatabaseName
MODIFY FILE( NAME = logicalname, FILENAME = 'M:\newpath.mdf')
Step 4 : Stop SQL server service, move the database file and start SQL server Service
Step 5 : Check that the update is ok with the query below :
SELECT
name as logicalname, physical_name as filename
FROM
sys.master_files
WHERE
database_id = DB_ID('yourDatabaseName');
You can follow the same way to add a file to a log shipped database.
Step 4 : Stop SQL server service, move the database file and start SQL server Service
Step 5 : Check that the update is ok with the query below :
SELECT
name as logicalname, physical_name as filename
FROM
sys.master_files
WHERE
database_id = DB_ID('yourDatabaseName');
You can follow the same way to add a file to a log shipped database.
29 May 2014
user is not able to access the database "msdb" under the current security context.
Symptoms when the guest user is disabled in the msdb database
USE msdb;
SELECT prins.name AS grantee_name, perms.*
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS prins
ON perms.grantee_principal_id = prins.principal_id
WHERE prins.name = 'guest' AND perms.permission_name = 'CONNECT';
GO
If you receive a result set that resembles the following, the guest user has the necessary permissions.
How to resolve the issue
USE msdb;
GRANT connect TO guest;
GO
http://support.microsoft.com/kb/2539091/en-us
27 May 2014
Add Article Replication _Simpler Way
Use Test_Model
go
Exec sp_changepublication 'Test_model_pub','allow_anonymous',false
GO
Exec sp_changepublication 'Test_model_pub','immediate_sync',false
GO
sp_addarticle 'Test_model_pub', product5, @Source_object=product5
GO
sp_refreshsubscriptions 'Test_model_pub'
GO
sp_startpublication_snapshot 'Test_model_pub'
go
Exec sp_changepublication 'Test_model_pub','allow_anonymous',false
GO
Exec sp_changepublication 'Test_model_pub','immediate_sync',false
GO
sp_addarticle 'Test_model_pub', product5, @Source_object=product5
GO
sp_refreshsubscriptions 'Test_model_pub'
GO
sp_startpublication_snapshot 'Test_model_pub'
Subscribe to:
Posts (Atom)
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
-
Error messages: · The process could not execute 'sp_repldone/sp_replcounters' on ' SCKNLSSI '. (Source:...
-
Error Message: Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been...