I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.
View 9 RepliesI need to recover some data in a table but i'm not 100% sure the right way to do this safely.
I'll need to query the two tables to compare the before and after but how do i go about restoring/attaching the backup database to SQL without causing conflicts?
If I restore, I assume this would just overwrite which is obviously the worst thing that can happen. if i attach the backup, how does this affect the current live DB? how do i make sure that it's not getting accessed and mistaken for the live DB?
The SQL server is 2008 R2 running as a VM.
I have a database with deployed CLR assembly on SQL 2008. I recently took a backup for the database and restored it on SQL 2012 server. Everything worked fine except very strange working of CLR assemblies functions.The result of the function is very odd. It manipulates the numeric string and return the result. It is returning very strange result on SQL 2012 as compared to SQL 2008.Example SQL 2008 ManipulateString('1234') returns 2345 On SQL 2012 the same call returns 1155.I am also trying to deploy the same assembly to SQL 2012 server database. I have successfully register the assembly, but the database do not list the exposed methods.
View 5 Replies View RelatedI have a table that contains records of transactions with ID column is primary key
I use partition follow ID column, each partition have 1 million records.
CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)
CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])
But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
i would like to know it's possible to find all transaction(insert, delete,update) on a database for a day. if yes what can i do.
View 2 Replies View RelatedThe database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I have been restoring database backup from server1 to Server2 and both database name is same till today. But I had an error today. I verified path, location and access everything is there but no luck to resolve.
CODE:-
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastGEFullBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
DECLARE @GEFullPath NVARCHAR(1000)
[code]....
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies View RelatedI am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies View RelatedI was contacted by the SAN team to test backup/restore of larger databases using a split-mirror backup (BCV) or clone that is taken from production db server and copied to another sql box. They want to use this process once a week. I see the mounted drives with the data/log files. All looks good. Initially I attempted to attach the databases and received (Unable to open the physical file db.mdf Operating System Error 5 Access is denied). I manually granting SQLServerMSSQLUser$<computer_name>$<instance_name> on all of the physical files 20 total. That worked.
Since this will be weekly, the SAN team performed the copy again and now none of the databases can communicate with the newly copied files. NTFS permissions need to be set again. I'm getting (Operating System error 21: the device is not ready). Is there something that I'm missing in this process how the vendor BCV clones the data and SQL communicates with the copied files as I was thinking it would be more automated process?
I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method
Here is an example of what i am doing
DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
the above query produces the following result
EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh
How do I join the data coming out from the above query with another table in the database, for example Employee in this case
i.e. somewhat like this (note the following query does not perform the join correctly)
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID
My desired output after the join would be
EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin
I've never worked with a column that's defined as a binary data type. (In the past if ever we had to include a photo we stored a link to the JPG/BMP/PNG/whatever into a column, but never actually inserted or updated a column with binary data.But now I've got to do that. So how do you put data into a column defined as BINARY(4096)?
View 3 Replies View Related1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
when prod db is restored in test server, i have to hide/replace sensitive fields (name, phone etc)Is it doable in SQL 2008 and SQL 2012?
View 5 Replies View Relatedfollowing is the code which i am trying to use
it throws an error and dosent work.
error details:
Unable to cast object of type 'System.Data.Linq.DataQuery`1[tbl_temp_bank]' to type 'tbl_temp_bank'.
source code(aspx.vb file)
Dim c As New temp_business_bankDataContextDim tag = From t In c.tbl_temp_banks Where t.TIN = Convert.ToInt32(tin.Text) Select t
c.tbl_temp_banks.DeleteOnSubmit(tag)
c.SubmitChanges()
please can some one help me with this.thanks
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,
[Code] ....
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
Can I safely use top n select/delete in a while loop? For example:
declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.
What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.
Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).
This is on SQL Server 2008R2.
I am using a SQL Server Agent jobs that run each morning to update the records in a table to match what they should be for that day. I built them and tested it using a test table called "testtable1". It worked fine. But when I switched over to our production table, it fails saying the table has to be decaled. What would be the difference. The production table has a "@" in front of the name, is that causing issues?
USE [Live_build]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
DELETE
FROM @ZIPLIST
INSERT INTO @ZIPLIST
SELECT * FROM tblZip3DSWed;
END
Is there is 'fairly' simple query to delete duplicate rows from ANY table ?
A script which removes duplicates from a table, when a table_name is supplied.
I am experimenting with using CDC to track user changes in our application database. So far I've done the following:
-- ENABLE CDC ON DV_WRP_TEST
USE dv_wrp_test
GO
EXEC sys.sp_cdc_enable_db
GO
-- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TEST
USE dv_wrp_test
[Code] ....
The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4)
drop procedure dbo.spdv_AVAUpdate
[Code] ....
When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE
USE dv_wrp_test
GO
SELECT *
FROM cdc.dbo_AVA_CT
GO
--RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4
--__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
--0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL
--0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000310x0F119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000420x0F119Test46NULL
Why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?
this directory is over 2gb
Program FilesMicrosoft SQL Server100Setup BootstrapUpdate Cache
can you delete the contents safely?
I am new to mssql server. There is a table on one of my databases that occupies a lot of space. And the space usage is as follows: (all values in KB)
reserved: 42329064
data: 16272288
index: 26050032
unused: 6744
This table takes up almost 80% of my database size, and the information is this table just captures the time spent by a user on the website(not very critical data)
I would like to know how to delete the entire index (which is what is occupying most space) to free up disk space. the index is a clustered index.
I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). How to archive the data and then delete the archive data from both tables.
View 9 Replies View RelatedI have a execute sql task which insert data into a table, but before that I need to make sure no data is available for particular date. we have a table where we are defining date for which date we want to data, and we are fetching that particular date using ssis variable 'Date'. today I was trying to load data dor '2015-05-10'. But it gave me error like '[Execute SQL Task] Error: "Value does not fall within the expected range.'. Below are the my execute sql task query:
delete from STG_Shipped_Invoiced
where Transaction_Date=?
INSERT INTO STG_Shipped_Invoiced (div_Code, inv_inv_id, tot_Net_Amt,
trans_date, trans_type, Created_date, Transaction_Date)
select inv.DIV_CODE as Div_Code, inv.INV_ID as inv_inv_id, inv.TOT_NET_AMT as Tot_Net_Amt,
[Code] ....
Here I have defined a variable 'Date', where we are passing the date. Am i doing anything wrong with the delete query?
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
Hi,
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
Thanks,
I have a requirement that I need to reload the last seven days worth of data each night to ensure that we pick up late arriving and updated records. To avoid having to do updates we delete the last seven days data and reload.
I was wondering if it is possible to set up the table as a partition, paritioned on a value (OLD, NEW) or similar.
The job would set the last day in the NEW partition to be old, the theory being that this would cause the rows to move to the OLD parition, and then truncate the new partion rather than deleting. The last seven days data could then be inserted into the empty new partition.
My questions is 1. Is my theory about the data moving from one partition to another correct. 2. Can I actually truncate and individual parition, 3. Do you think it will perform any quicker. We would expect data in the range of 100K to 500K rows in the seven days and will store up to 4 years of historical data.
Thanks for your thoughts
Stapsey
Hi champs!
The data i have is like this:
nr date value
------- -------------- --------
1 2007-10-03 45
1 2007-10-05 5
1 2007-10-11 -1
1 2007-10-30 23
2 2007-03-03 3
2 2007-03-13 -5
2 2007-03-03 6
3 2007-10-03 42
3 2007-10-03 11
.....
I want to summerize the value in each group and set the date to the 1'st og that month
i.e.
nr date value
------- -------------- --------
1 2007-10-01 72
2 2007-03-01 4
3 2007-10-01 53
any help is much appreciated.
thanks
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'