SQL Backup/Copy Records
Aug 31, 2001
Hi i am looking for some advice about sql databases and wondering if anybody can help me.
First the Background.
I am in the process of writing a payroll program that uses SQL as a data store. However rather then creating individual databases for each payroll that can be ran on the system i have created a core payroll table that generates a unique id for each payroll and then reference that thoughtout the system.
Now The Problem
The problem I now have is when I work out the final pay for the employees I want to make a quick copy of the various records just in case I need to undo the net pay calculations for adjustments to the data, it is effectively rolling back the transactions to the database but I want to be able to keep them till I decide that everything is correct.
I cant take a snapshot of the entrie database because the payrolls stored within may be in all sorts of states.
I need to keep the records for just the payroll I'm working on.
Any Ideas or Solutions !!!!!
Thanks
EJ Gibbons
"Where poets speak there hearts, then bleed for it" - U2
View 1 Replies
ADVERTISEMENT
Dec 29, 2014
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
View 4 Replies
View Related
May 6, 2015
I need to backup a single DB with "Copy-only Backup" option on. I Want to place the backup on a different location, we have a media set which is used for daily backups. I want this backup placed on a different
server 192.168.250.xxx
DepartmentsITBackup2015xxx.bak
I have added this location to "back up to" The old one is still there. I I then hit OK th backup hangs for 2-3 minutes and gives me an error which points to the old media set. SO I want to create a new media set and call it "onlyonce" but what I am not sure about is what this Means "back up to a new media set and erase all existing backup sets" Does that mean all old backups will be deleted?
View 3 Replies
View Related
Aug 28, 2004
I have 1 table. In that table there are thousands of records. I have an ID that is autonumbered for a unique id.
What i need to do is at any given time select as many records from that table that have a certain Forein Key in it. Take all those records and copy them back into the same table but at that time i need to change some of the values including the Forien Key and the name.
Is there a bulk way to do this without having to loop? If so can you show me.
If not can someone show me the best way to get all the records and copy them back into its own talbe with a new Forien Key and Name.
View 2 Replies
View Related
May 24, 2008
I have 2 tables.
TABLE 1: AttachmentTypeDefaults (fields: DefaultId (pk), AttachmentType)
TABLE 2: AttachmentType (fields: AttachmentTypeId (pk), CompanyId, AttachmentType)
The @CompanyId variable has to be the IDENTITY of a new record that was just INSERTed into another table just before (in this new SP.)
What I want to do is loop thru the AttachmentTypeDefaults table and INSERT every record into the AttachmentType table and use the @CompanyId for every new record. (AttachmentTypeId will create itself.)
How would I do this?
Sounds easy but I am new.
I would like it to be compatible with SQL Server 2000 also.
I tried a query like this, but I don't think I'm close...
declare @MyNewIdentity int
SET @MyNewIdentity = SCOPE_IDENTITY()
-- Now INSERT default attachments
INSERT INTO AttachmentType(CompanyId, AttachmentType)
VALUES(
SELECT @MyNewIdentity AS CompanyId, AttachmentType
FROM AttachmentTypeDefaults)
View 6 Replies
View Related
Mar 20, 2008
HELP. I've inherited some less-than-stellar dessign.
I have a table:
tbMD
ObjectID uniqueidentifier not null
Tag nvarchar(50) not null
Data nvarchar(400) not null
This table has 1.6Billion records and has filled up the drive (nearlly 1 TB in the DB, with about 600GB in this one table).
I need to move this table to another drive with ZERO down time.
I have created a new filegroup on a second drive array and created a new table (tbMD_TEMP) and my thought was to copy all the data from tbMD into tbMD_Temp then drop tbMD, and rename tbMD_Temp.
So, anyone have any great ideas on how to get this done very quickly?
View 5 Replies
View Related
Apr 12, 2000
hello,
I was storing data on sql server with license for 60 days. before license expired I did:
in enterprise man. selected DB, right click, selected All Tasks,
selected Backup. Backed up to MSSQL7BACKUP DIR using same name.
then went to other machine installed sql server and from the enterprise manager did a restore DB with "restore on existing DB" option
the problem is that I try to give a user rights to the DB and I get error message: error 15023 user or role already in DB
any hints,
thanks
View 1 Replies
View Related
Jan 9, 2001
Hello,
I need to backup the database to file and then copy it to Tape. Can anyone guide me how I should do it. I need to automate it.
Thanks.
Rea
View 2 Replies
View Related
Apr 26, 2008
hi
i am using isp_backup store procedure to get the daily backup of database and this is work fine but i have copy the database file in network sharing folder to use
following cmd
EXEC master..xp_cmdshell 'D:filename M:foldernam'
M: is network folder to window2003
but their no copy this folder
thank
View 2 Replies
View Related
Jun 4, 2004
Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).
So far I have two stored procedures:
1) sp_copyDetailsOne
/*Create a recordset of the desired items to be copied*/
CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS
SELECT itemId, itemPrice, itemDescription, itemQuantity
FROM tblSalesItems
WHERE (invoiceId = @invoiceIdFrom)
OPEN @outCrsr
2) sp_copyDetailsTwo
CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT
/*Allocate a cursor variable*/
DECLARE @crsrVar CURSOR
/*Execute sp_copyDetailsOne to instantiate @crsrVar*/
EXEC sp_copyDetailsOne @invoiceIdFrom, @outCrsr = @crsrVar OUTPUT
/*Loop through the recordset and insert a new record using the new invoiceId*/
FETCH NEXT FROM @crsrVar
WHILE @@FETCH_STATUS = 0
BEGIN
/*Run the insert here*/
INSERT INTO tblSalesItems
(invoiceId, itemId, itemPrice, itemDescription, itemQuantity)
VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)
/*Fetch next record from cursor*/
FETCH NEXT FROM @crsrVar
END
CLOSE @crsrVar
DEALLOCATE @crsrVar
My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.
Thanks
View 2 Replies
View Related
Sep 24, 2012
In a job of migrating from an old database to a new one (with other structure, other server, other version) i'm copying from the source old tables and inserting into the new destination tables. The problem is that some records have inconsistencies (of any kind) and thus are not inserted due to foreign key, not null, etc validation. When a problem occurs none record is copied! and there is my question: How can i perform the copy in wich it copies the good records (without inconsistencies) and leave aside the bad records. I also want to know wich were not copied and better if in the copy process those were put in a temp table or exported to excel for further analisys o its data.
I'm using this model of "migration":
BEGIN TRY
INSERT INTO DESTINTATION_TABLE (
col_d1,
col_d2,
col-d3,
...)
SELECT
col_s1,
dbo.some_function(col_s2),
col_s3 * 100,
...
FROM SOURCE_TABLE join <other_table> ... where <some filters>
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
END CATCH
(for now, with try/catch I've only get to know the error occurred, if some)
View 5 Replies
View Related
Dec 11, 2013
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
View 5 Replies
View Related
Jul 20, 2006
How could I go about doing the following:
I have a column with lets say
AHA1
AHA2
AHA3
AHE1
AHE2
ARL1
ARL2
How could I make a column next to it that is basically the same but cuts off anything after the third letter? So this new column would look like:
AHA
AHA
AHA
AHE
AHE
ARL
ARL
Thanks!
View 4 Replies
View Related
Jan 4, 2000
What is the syntax for using xp_smdshell to copy a file from 1 server to another?
Our Report server is restored from our production server and I want to copy the .dat
file from the production server to a folder on the report server.
View 1 Replies
View Related
Sep 14, 2004
Hi,
I am creating a job that runs this command:
EXEC xp_cmdshell 'copy f:dados_sqlmssqlackup
ecom_tb_basico.bak \stalingrado_2c$ emp',
The result of this job is:
"Access is denied.
0 file(s) copied.
NULL"
But I am running this command with sa user.....
Wich kind of permission is missing to execute this copy?
When I execute the same command to copy the backup from the server to itself, it works fine!!!!
Does someone have an idea to solve this problem?????
View 3 Replies
View Related
Jan 23, 2004
Hi people!!
I was wondering how u can copy a sql server database (to make a backup) on to either a CD or floppy(dunno if theres enough disk space for that)???
Thanks
View 1 Replies
View Related
Jun 10, 2006
Hello,
i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok?
Autoclose = true and recovery model = simple.
Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections).
Can i copy at any time even when transactions are active?
I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.
This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..
Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?
Is this enough to have a valid backup? Backup would be an attach of the .mdf file.
Or must i use the BACKUP and RESTORE stuff? Why?
If so, for what reason is the AUTO CLOSE property there?
Regards,
Markus
View 11 Replies
View Related
Oct 24, 2007
I have a table on an offline database having let say 2000 rows and same table on an online database with let say 3000 rows. Now I want to copy just those records (1000) from online database that are not in the offline database table. Also, both tables have same name, same schema etc.
View 3 Replies
View Related
Apr 6, 2008
Hi,
I already submitted this type of question before and i receive reply. But unfortunately i found out errors when performing on my system.
My problem regarding to this one:
Suppose i have two databases with same tables with different records and I would like to copy the records from one database to another data and vice-versa. So that both the tables contains same number of records inside the tables.
Example:
Database1 (EmployeeTable) contains 6 records.
Database2 (EmployeeTable) contains 10 records.
It should copy only those records which is not present in each other database. No duplicate records.
Before i was recommend to use Primary key, if it is not present use index.
Hope this time i could solve my problem.
Thanks.
Kashif Chotu
View 1 Replies
View Related
Oct 9, 2014
SQL Server 2012-SP2 and Windows 2008R2
I need to copy the last differential backup file from the production server directory to a data warehouse server directory. There are 3 differential backup files on the directory and I need to grab the latest one. I have the following syntax which work from a batch file but it does not work when I put it into a job step as a cmd even though I remove the double %% to only one %.
echo @off
set path1="192.29.305.213$SqlserverProductionBa ckupsKBR_PROD"
set path2=K:SqlserverDatawarehouseBackupPROD
for /f "tokens=*" %%a in ('dir /b /a-d /o-d "%path1%DIFF*.bak"') do copy "%path1%\%%~a" "%path2%" & goto nextstep
:nextstep
Echo File Copied.
Even when I change it to (only one % for the variable),
for /f "tokens=*" %a in ('dir /b /a-d /o-d "%path1%DIFF*.bak"') do copy "%path1%\%~a" "%path2%"
It does not work..........It runs successfully but no file is copied or I get
Message
Executed as user: PROD23Sqladm024. The process could not be created for step 1 of job 0xEAAF943771FF304A9E7AD8ADAC24F96C (reason: The system cannot find the file specified). The step failed.
I know that the file is there. It works with batch file. Poweshell script can be OK. No SSIS - Not installed
View 6 Replies
View Related
Sep 23, 2014
I've an emergency requirement to copy Source server database backup files to destination Server through xcopy command. Backup job on source server runs daily, so once this job get completes all databases backups needs to be moved to destination server. But here the main concern is "the backup files on destination server shouldn't be overwritten, they should be placed separately as Source server job runs daily".
We've a command which overwrites backups on destination server. But we need to keep backups on destination at-least for 4 weeks (means : retention should be 4 Weeks).
View 5 Replies
View Related
Sep 19, 2007
Hello,
We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?
Thanks in advance.
View 7 Replies
View Related
Aug 26, 2014
I have been asked to give the users the ability to copy a set of records within the database. The current setup is
Master Table: JSA1
JSAID: (PK, int, not null)
JOBTITLE(nvarchar(200), null)
PlantNumber(int, not null)
Detail Table: tblSteps
STEPID (PK, int, not null)
JSAID (FK, int, not null)
StepNo (int, null)
BasicSteps (nvarchar(200), null)
DBPhoto(varbinary(max), null)
The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.
I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
View 4 Replies
View Related
Apr 22, 2015
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
View 4 Replies
View Related
May 24, 2015
I am having three table tblTest,tblTestQuestion,tblAnswers
Each test can have multiple question and each Question can have multiple answers.
Now I am already having records in database. I wants to create clone copy of existing test except testdetails in tblTest because the test will be unique, and then insert questions and answers into their respective tables.
I was trying to create SP but stuck.
Please find below tables structure
[code="
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAnswer](
[Code].....
View 5 Replies
View Related
Jul 9, 2007
I have an application which stores records on a local SQL Express and I need to move the records to a SQL 2000 database. I have the SQL 2000 server linked in the Express Management Console (under Linked Servers). I'm trying to use a stored procedure to accomplish this, but get an error "Invalid object name 'ngtxa4-rsmsz-01.newpurchase.tblRequest'." Express uses a table named tblTRequest in the TempPurchase database, while 2000 uses a table named tblRequest in a NewPurchase database. Here is the stored procedure I'm using:
USE [tempPurchase]
GO
/****** Object: StoredProcedure [dbo].[InsertRequestToMain] Script Date: 07/09/2007 08:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertRequestToMain]
AS
BEGIN
INSERT INTO [ngtxa4-rsmsz-01].newpurchase.tblRequest
(fldRequestDate, fldRequiredBy, fldUserID, fldWorkAreaID, fldVendorID, fldClassID, fldEstimate, fldMemo, fldStatusID, fldStatusDate, fldSystemID,
fldtmpRequestID, fldUpdateCode)
SELECT fldRequestDate, fldRequiredBy, fldUserID, fldWorkAreaID, fldVendorID, fldClassID, fldEstimate, fldMemo, fldStatusID, fldStatusDate, fldSystemID,
tmpRequestID, UpdateCode
FROM tblTRequest
WHERE (fldHold = 0)
END
Any assistance with this would greatly be helpful. Thank you.
View 2 Replies
View Related
Feb 10, 1999
We are having a problem with trying to backup the database device and log DAT files located in the MSSQLData directory.
The Seagate Backup Exec. states that the files are busy and skips them during its backup cycle. It skips all the devices in the
directory.
Any suggestions?
View 3 Replies
View Related
Mar 9, 2005
Hi,
Can someone please express their experience and recommendations for the following questions?
1. Do you need to unable SQL-Backup if you already using a 3rd party software utility to backup the SQL data drives?
2. If you do not want to shutdown SQL services for backup can you use Volume Shadow Copy as a solution to open files?
3. What size would you recommended to use for the Transaction Logs DB if my Data DB size is 2GB
I am running Windows 2003 with SQL 2000/SP3. Obviously, I am not an expert in SQL so I appreciate your help.
Thanks
View 2 Replies
View Related
Jul 11, 2014
We have just implemented a SQL 2012 always on environment. We have a primary and secondary server. I am confused about how to set up the backup plans. The application team was happy to tell me that in sql 2012 always on we can offload the backups to the secondary, thus reducing overhead on the primary server.
However, the secondary only supports copy only full backups. I am unsure how these would be useful in a disaster event? I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?
View 7 Replies
View Related
Nov 21, 2007
I'm looking for a way to copy/migrate all of my SSIS packages from 1 SQL2005 server to another SQL2005 server. I see export/import options but they are for 2000 DTS packages. And it seems like I can only do this one package at a time, which is tedious. Anybody out there who's done all packages at once?
Thanks!
View 9 Replies
View Related
Apr 7, 2006
How can I copy a table from my sql server 2000 db to my sql server 2005 express edition?
I have a project in VS.NET 2005 and I have a db in App_Data folder. However, when I look into that folder, there is nothing visible. I now need to copy a table from my existing sql server 2000 to my db located in my project's App_Data folder.
Any help would be appreciated..
Regards,
View 1 Replies
View Related
Oct 22, 2015
I am copying data from database to an excel file through SSIS. database is MS SQL 2005 and BIDS is also 2005.However, the job doing this task fails every time.As per investigation, the result of the query is more than 100,000 rows and we know that excel has a limit of 65000 rows of data.Is there a way of setting the limit up? or something? a better approach maybe so that everything will be copied to the excel file successfully.
The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error Error: 2015-10-22 04:34:58.05 Code: 0xC0047021
Source: Data Flow Task
Description: SSIS Error Code DTS_E_THREADFAILED.
Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:30:00 AM Finished: 4:35:05 AM Elapsed: 304.844 seconds. The package execution failed. The step failed. "
View 1 Replies
View Related
Nov 6, 2015
I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.
An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.
View 7 Replies
View Related
