For copying the SSISDB from one server A to another B I used the following steps:
Took backup of SSISDB on server A.
On Server B, created a new SSISDB catalog.
Restored the newly created SSIDB on B from backup of the DB from server A.
Is it correct process for SSISDB or not.
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).
Need to scheduled to copy table data from one database to another database daily @7:00 AM by creating the table with date and time stamp.
Example :
Test1DB ---> Table1
Test2DB -----> Table1_281120140700 in Day1 and Table2_291120140700 in Day2.....and so.
SELECT * INTO Test2db.dbo.new_table_name FROM Test1db.dbo.old_table_name in this format ??
script and scheduled to run daily @ 7:00AM?
I have a tree and I need to copy a nested sub-tree (an element with its children, which in turn may have their owns) from one place to another.
The system should allow to handle up to 8 levels. I do know how to move, but cannot figure out how to copy.
Below is a working example With Create Table, Select and Cut / Paste (implemented via Update).
I would like to know how to copy a nested tree with reference id 4451 from Parent_Id 1 to Parent_Id = 2
--***** Table Definition With Insert Into to provide some basic data ****
IF (OBJECT_ID ('myRefTable', 'U') IS NOT NULL)
DROP TABLE myRefTable;
GO
CREATE TABLE myRefTable
(
Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,
[Code] ....
How to Copy nested sub-tree 4451 with all its children to Parent_Id 2, without deleting from Parent_Id = 1 ?
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].....
how to copy a content of a table from one remote server to another,. server A does not see server B (B doesn't see A) - I cannot even ping to one from another.I do have SQL Studio installed on server C, which IT team configured to allow access to both A and B.So what I did so far is to periodically:
1. connect from the studio on server C to server A
2. run the following script on server A: SELECT * FROM A.myTable FOR XML PATH('ROOT')
3. copy the result
4. connect from the studio on server C to server B
5. to write something like
DECLARE @xmlData XML;
SET @xmlData = pasting here my result from item 3 above
6. INSERT INTO
SELECT
ref.value .....
FROM @xmlData.nodes('/myElemnet/ROOT')
xmlData( ref );
so it works. now there is a requirement to schedule this update to run periodically and I need to implement it..
I have two servers on different machines:
Production & Testing
I have a database called MyData on Production.
I can link the servers using sp_AddLinkedServer.
I want to make a copy of MyData from Production to Testing and rename it from MyData to MyData_Testing.
Currently, I figure I will have to make a backup - copy it from Production to Testing - restore it. Since I can link the servers, I can initiate the backup from Testing - copy - and restore from a script on Testing. The only part I am not sure of is how to copy the file via the script.
How to copy the file?
Using import task only can select and copy tables and views.
Since there are many objects at the same DB I don't need copy, is there any way let me select and copy some objects, such as functions, stored procedures?
I need to use Bulk insert statement for copying a table with 200 million rows to another table on the same server...the table has no primary key or identity column.... script for BULK INSERT ...
View 9 Replies View RelatedWe are using 208r2. We used to generate simple reports against to prod. The production is running slow. Just we need the production fresh copy all the time. We are using mainly one database. So I need to have read only copy of that particular database which is sync with the production.
View 3 Replies View RelatedI have a database DB1 and DBTest
DBTest Database is empty. I want to copy all the objects in DB1 - schema1 how can I do that?
I just completed a copy-Only compressed backup of a DB (with a FULL Recovery Model ) on SQL Server 2012 and the resulting backup (the bak file) is 1/100th the size of the data & log file. Is the compression in SQL Server 2012 just that good or did something else happen that I did not catch? Below is the T-SQL to re-create the backup. The size of the data file is 750MB and the log file is 75GB and is %95 used according to the SQLPERF command.
Does the compression in SQL Server 2012 simply that good
BACKUP DATABASE [MYBIGOLEDB] TO DISK = N'Z:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMYBIGOLEDB_20150611.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'MYBIGOLEDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
we would like something like Mirroring with a Snapshot database so that the "replicated" site can read the database locally.Unfortunately, this requires SQL Enterprise edition on the reading site if I get this right.Is there a cheaper way to achieve this sort of functionality?
View 9 Replies View RelatedI have this task, I have to copy from one database into a remote all record which contain a specific value into a specific column. (the remote Db destination tables are already created)
I have already identified all tables to copy (about 3000 with 60Milions of records), now how to implement the most performing way to copy records.
If most performing, the destination database can be placed on the source Sql instance, I can move the database on the destination instance after I have copied all records.
Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target. We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do. Permissions are different on each instance, so would prefer not to overlay users on destination database. Options we are looking at right now are...
Log Shipping
Snapshot Replication
Transfer SQL Objects Task (SSIS)
Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.
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?
How I can script out or copy an existing assembly to another SSAS box? I am kind of new to MDX.
View 0 Replies View RelatedI am working on a Login migration issue. I have the Master database from the original database, that I have attached as a User Database, call it OriginalMaster. I also renamed the mdf and ldf files, before attaching, just in case.
I want to select the logins from the OriginalMaster, and using sp_help_revlogin script all the Logins out. I know there were many logins in that database. But what I am seeing, are all the Logins from the Current Master database. Which are only 7 Logins.
How can I select the logins, or script out the Logins from the OriginalMaster database?
I have 3 table
table_A
table_B
table_C
TABLE_A
SNO NAME ID
1 RAJU 070491
2 VAMSHI 089767
3 ARUNA 068908
TABLE_B
SNO NAME ID
2 RAJU 070491
4 JKLKJ 098766
I WANT COMPARE TWO TABLES(TABLE_A & TABLE_B) SELECT TABLE_A MATCHING VALUES COPY IN TABLE_C
EX-
SNO NAME ID
1 RAJU 070491
How can I make a copy of an SSRS Report history snaphot to another folder in Report Manager. I have a report that has history snapshots. But want a copy of the report snapshot history in another folder for users to view easier in Report Manager.
View 0 Replies View RelatedSuddenly one day I found some corruptions have occurred in my SQL Server 2012 installation. Because my 'msdb' has been marked SUSPECT/CORRUPT; hence no new work can be done as per my schedules.
I have gone about trying to repair it. I followed this article here: [URL] [the region 'Create new MSDB Database'].
I am facing some sizeable difficulties in doing this on my own. Steps 1. & 2. I have done. I am now stuck at step no. 3.My instance name is SQLEXPRESS. So, when I give,
SQLCMD -E -S<SQLEXPRESS> -dmaster -Q"EXEC sp_detach_db msdb" [as per the article], what I get is:
The System cannot find the file specified.
So I am stuck at that point. What's with the 'cannot find file specified'? The stored procedure, or the mdf, ldf files for my msdb? What is it?Also, if and when I am through with step no. 3 I would like to know about steps 5. & 6. also which are soon to follow [like how to do them correctly, safely and from where, the different options]. This is a huge priority for me to get my sql server up and running again because I can't do my other coding works without it. Everything is stalled. Slowly reaching the desperation, SOS mode..
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 RelatedHi,
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Thanks for any help,
KT
Hi!
I did:
alter database mydb set single_user with rollback immediate;
exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got
>>Cannot copy <myfile>: Access is denied
Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success.
I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files?
Best regards
How do I transfer/copy the stored procedures in my Test DB to my LIVE DB? IT won't allow me to export keeps giving me an error.
View 4 Replies View RelatedHello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables.
How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.
I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:
We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.
Here is what I want to do:
I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.
Can anyone refer me to good 'recipes' or sources of information on thistopic??I have Visual Studio Tools for Office, which installs SS 2005 Expresslocally to my XP box, and I want to develop in SS 2005, then copy thetables or queries or reports etc. to a SS 2005 Standard server.Thank you, Tom
View 3 Replies View RelatedAny help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
View 2 Replies View RelatedI want to schedule a job which pulls files from a non SQL server (Sybase) which later needs to have a step 2 kicking the ssis package. Problem is that, on the source a batch file will run every 4 hours and outputs total of 10 text files. (takes 5 minutes complete). Now, on destination i want to pull these files via SQL job but while scheduling;
1. I don't see any option saying like 4 hours 10 minutes or so
2. If its out there, then i believe this might be a problem as this time would be an increment one e.g next run would be 4 hours 20 minutes in that case.
How should i achieve pulling these files up because we have an SSIS package on destination that needs those text files to be used as soon as they arrive on SQL server(destination)
Greetings,
I have two SQL Server tables on the same server and in the same database. I'll call them table A and table B. They have identical schemas. I need to insert all rows in table A into table B. (Don't laugh - this is just for testing and long run the tables will reside on different servers.)
Can someone please tell me the correct task to use for this and the connection type I need for both the source and destination?
Thanks,
Black Cat Bone
Hi,
how can I import (restore) a backup of SQL Server 2000 database to SQL Server 2005 Express?
Thank you all