Overview: There was requirement from business to test live server backup into DR server every month / week, not a completed DR. It was about 30+ databases with total of 500 GB. Steps: Add net use command in sp_DatabaseRestore stored procedure Share backup folder path in source server “\” Run the find the recent backup script on source server Script out and run it on target server where you have created sp_DatabaseRestore Run the restore command in SSMS or SQL agent job. Test Restore with execute N parameter and make sure everything is correct and run with Y. I have started using Ola Hallengren’s maintenance script and searched for…
-
-
Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal cannot be enabled for Change Data Capture because a database user named cdc
Overview: There was a database restore from production to UAT and the restore was done without Keep CDC option. When developer asked to enable CDC the following was the error. Always keep CDC while restore is best option whether we have CDC or not. use [DB Name] go exec sys.sp_cdc_enable_db Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 49 [batch start line 2] The database ‘DBName’ cannot be enabled for Change Data Capture because a database user named ‘cdc’ or a schema named ‘cdc’ already exists in the current database. These objects are required exclusively by Change Data Capture. Drop or rename the user or schema and retry the…
-
identity jumping from default to bigger large number
Overview of the issue: It is small web based productions application and hosted in SQL express 2012 for the license costing. Issue is whenever SQL restarted for whatever reason identity numbers are changed very bigger and identity jumping from one to other digits. To fix this in our case we cannot do this at database level or table schema script level to NOCACHE or disable the IDENTITY CACHE, since it is been enabled many databases and application code changes are not advised. Resolution: Enable trace flag T272 at instance level in the start up parameters.
-
Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal
Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 198 [Batch Start Line 8]Could not update the metadata that indicates database is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15404: ‘Cannot not obtain information about windows NT group error code 0x5. Change your database owner to SA. use [DB_name] go Exec sp_changedbowner'sa'
-
Cannot connect to WMI provider. You do not have permission or the server is unreachable SQL server 2005
Overview of the issue. We had two SQL instances are installed in a server SQL 2008R2 and 2014 both instance configuration managers are not opened. Error: Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Fix: Open drive where you have installed SQL, mostly C and navigate the folders and make sure you have file called – sqlmgmproviderxpsp2up.mof. Run the following command in the CMD with run as administrator. mofcomp “%programfiles(x86)%\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof” Ex: 120 need to be changed for other versions. 2005-90 2008R2-100 2012-110…