USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GOand I have enables cdc on tables within the database using
USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1
GO
As per [URL] ....
Data changes are then stored in a system table called cdc.dbo_MyTable_CT So far so good.I backup the database and restore it
backup database MyDB
to Disk = 'CBackupMyDB.bak'restore database MyDB1
from disk = 'CBackupMyDB.bak' No table called cdc.dbo_MyTable_CT in system tables!
Have I missed a step? and is there a way to get these tables into the backup.I am now building a ssis job to move the contents of the cdc.dbo_MyTable_CT to an archive table, but i am worried that if I had to restore the database to a point in time I may not be able to do it for these tables.
I am using change tracking for tracking changes to an OLTP database for daily data warehouse load. I am tracking changes on 4 tables:
Site, Well, GasEngine, GasField,
I am using an SSIS package for our DW load and on completion, I store the results of the queries above into my own ChangeTracking table. I then use this as the last synced version for each table which I then use for the next DW load. I have the following change tracking settings:
CHANGE_TRACKING = ON ( CHANGE_RETENTION = 10 DAYS, Â Â Â Â AUTO_CLEANUP = ON )
Now, the Site and Well tables may not have any changes for a few weeks or months but the GasEngine table will have changes every half hour and the GasField table will have changes every week. I have recently received a NULL value for Site changes query (below) and the SSIS package fails.
Is this due to there being no changes within the 10 days retention period?? What can I do in order to maintain the changes?? Could I maybe update the scripts above to check if the response from the query is NULL and if yes, set the current synced version to the last synced version from my ChangeTracking table?
Also, when the 10 days retention period is up, will the change tracking change version counter continue on from the last change version?
I have a two problems : 1 . I want a backup a database of sql and I try it only after I close the program that called to this database and only after a 1 hour .How can i contol it? 2 . I have a tables sql in C# and when i write a data of string in colum of int - i get an error , how can i get the error befor its happen and throw to the user my error and i want to menage it's? I very hope that you understand my problen and you can help me . Have a nice day. Tamir Bloch
I need to backup data (which satisfies a condition) from tables to flat files.....it worked fine using bcp...now i need to delete the records from tables which have been backed up......is ther any way out???????????
i wrote that bcp commands in a batch file to automate it.....
I'm having difficulty searching for an answer to this challenge. Cansomeone give me a clue on the right keywords to use to find adiscussion on this subject? All of the ones I saw appear to touch onolder versions. I'm working with SQL Server 2000, and need to backupand restore only tables, because the full backup/restore appears tomess up users/security, or something or another.Thanks for the assist.
I ran the following code on one of our servers and I got some surprising results. Those results included backups for that machine but also showed entries for other servers. I don't understand it! I can understand the results being wrong because I coded something incorrectly, but why would another server be listed in bs.server_Name? It appears that the entries are "correct" in that backups were taken on the other server at the time given, but why is it there??? (I put the code together to list the most recent backup for each database on the instance. I hope it is correct.)
select @@ServerName, bs.server_Name, bs.Database_Name, bmf.physical_Device_Name, bs.backup_start_date, bs.backup_finish_date, Case When bs.backup_start_date < Getdate() - 1 Then 'Stale' Else 'Recent' End as Status, Case When bs.backup_finish_date is NULL Then 'Failed' Else 'Successful' End as Success from msdb.dbo.backupset bs Join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id where type = 'd' and bs.backup_set_id = (select max (bso.backup_set_id) from msdb.dbo.backupset bso where bso.server_name = bs.server_Name and bso.Database_Name = bs.Database_Name and bso.Type = 'D') order by database_name
Version: 10.00.1600 (I believe the first 2008 release)
I made a backup from the master, model and msdb system tables. Now I travelled to a different country, and I only have the backups and would like to access them. I was able to restore the master db to a new installation of mssql but, it wont start. I do know why: as I read after I quickly realised that master backups take the accounts, serverID, groups, directory structure, and lots of other things.Basically I have no chance of doing a master restore. The question here: Is there any way of getting my data out from the master backup file (thats where my stuff is)? I'm thinking of "restoring" it to a separate non-master db, table, anything. I know now that I did the most stupidest thing ever, but believe me I don't want the last half years work to just disappear.
In SQL 2008, there is a way to backup a database, but specify the tables backed up, and, during the restore on the destination server, restore only those tables in the database?
What is the best procedure/sequence to reduce some tables containing large number of rows of a SQL 2000 server? The idea is first to check which tables grow extremely fast (all statistics, user or log tables), reduce the table according to the number of months the user wishes to keep in the table. As a second step backup remaining rows of table as txt files on harddisk (using DTS), UPDATE STATISTICS and re-indexing reduced table. Run DTS Package every month once (delete oldest month and backup newest month) and do the same as above to keep size of tables adequate. What is a fast way to reduce number of rows of a large table - the following example produces an error (timeout expired) of my ADO connection when executing: SET @str = 'DELETE FROM ' + @ProcessTable + ' WHERE ' + @SelectedColumn + ' < DATEADD (m,' +' -' + @KeepMonthsInDatabase + ', + GETDATE())' EXEC (@str) Adding ConnectionTimout = 0 did not help unfortunately.
What is the best way to re-index the table just maintained?
I have a database file approximately 30GB in size which creates 3 datatables daily. I need to reduce the size of the DB due to disk sizelimitations. Could I EXPORT some tables, let us say over thirty daysold, to something like DB_Name_Archieve and them delete them from theproduction DB to reduce the size? I also need the ability toreference/read them at a later time as needed.As a side note, could SQL Server use data on a redirected drive i.e.NET Used. This would allow me to move the old tables to the redirecteddrive and reference them as needed.As always, thanks in advance.Peter
How do you track changes to objects in SQL Server. For example changes in stored procedure,views and indexes. What system table or column track or indicate changes in text of sp or views. Help appreciated.
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
OK. For DDL, please refer to the classical Northwind ORDERS table,problem/challenge, find the longest duration (start_date andend_date), during which, no orders were placed.FYI, column names by the order of colid per syscolumns:OrderIDCustomerIDEmployeeIDOrderDateRequiredDateShippedDateShipViaFreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountryAny idea/approach? TIA.
Hi I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.
Yesterday I had problem with that a sp that contained join that the developer had forgotten the where part. The sp join very large tables and sp took more 95% dual itanium processor. How do fastest track this down when it accrues. Find the sp or sql that running and consume this much processor resources. I tried with a lot. Sp_who, sp_who2, activity monitor.
im trying to create a procedure that will insert/update a small table that has an ID, AmtBefore & AmtAfter.
this is just a table that i'd be using to monitor other activity on the database.
i'm really pulling a price + ID every hour and i need to track that somehow and know when the procedure runs if a price is lower than it was the last hour, plus of course keep the ID's straight. does anyone have a good approach to something like this?
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
I have several transaction tables on which I need to track the changes. That is I need to maitain the history of changes. Only few column values are changed often.
Which is the best way for tracking the changes.
1.Store the whole record after the change ?
Or
2.Store the ColumnName & its respective old & new value ?
Or any other better.
Note : UI part & SP's will take care of the tracking & no plans for triggers.
I have a table in my database and it holds some important information,I want to track the users who are executing DML commands(select,update,delete) on the data in that table. Is there any way do that?If so how could i implement that.can any one help me in this regard? Thanks in advance...
First thank you for all your help thus far. Now I'm stuck again. I've been doing a lot of reading on triggers and logging information into tables but I've been trying to capture how many times someone enters an item into the search box.
So every time somebody types Gumballs into the search box I want to capture it and the name of the person who is currently logged in. Is there away to do this? Maybe this is something that I should be checking in ASP.NET forums?
Hello everyone, I have a fairly unique need :) I am trying todetermine the use/clients for databases in my corporation that I ammaintaining, but that noone seems to know what they are for. Many ofthese databases never seem to have anybody connected to them in thecurrent activity.What I'd like to do is find a way to audit the logins, so everytimesomeone connects to a database it simply logs the clients IP address,what login they used, and maybe what time. I've been searching googlefor this and have found tons of information on auditing the logins,but not the clients, such as by ip. Any help in this regard would beGREATLY appriciated!Joshua
I have a device application that simply needs to upload data to a server. The preferred DB server is Oracle but I've made it work using RDA and SQL Server. The problem I'm having is that it just needs to upload data, whichh I send using the RDA.Push() method. The data arrives just fine, the first time. With every subsequent upload all of the previous data is deleted fromt he server. Apparently RDA is tracking the deletion of the previously uploaded data locally and on the next .Push deleting that data from the server.
My question is: Is it possible to prevent RDA from deleting data on SQL Server? I attempted to delete the rows from the __sysDeletedRows/__sysRowTrack tables but got a "Data is read only" error.
I hoipe someone can point me in the right direction here.
I have an application with the following requirements (using SQL CE 2 alas)
A set of tables on the server that need to be imported to the handheld. Using rda, I need to get the modifications to these tables from the server (add/edit/delete) but the handheld will never update these tables.
A set of tables on the server that need to be imported to the handheld. The handheld needs to add/edit existing records, and it needs to get any changes from the server.
A set of tables on the server where the handheld needs to import a subset of the records. It needs to add (but not edit) new records, upload the new records to the server, and download any changed (add/edit/delete) records to the handheld. What tracking options should I use in these 3 cases?
The problem comes in that I need to have some foreign key relationships in the database on the handheld. Since rda munges the names of primary keys (and indexes), I do not know of a good way to add these foreign key constraints. Any suggestions?
