COPYING A DB From One Server To Another

Feb 15, 2005

I was trying to find the fastest way to COPY a 50G DB from our production server to our test server. I was testing this on our test server and had a simple question.

Attach/Detach is out since I can only move not copy a DB using this method.
export/import, bcp and DTS take too long and are not indented for this anyways.

so, I was left with Backup and restore. so, I tried that on two different test servers. Took very long. so, I decided to be brave and try the following.

1. Take DB1 that's running on server1 OFFLINE
2. Manually copy all datafiles and logfiles from Server1 to server2.
3. Attach DB1 on server2.

It came up great. No complaints whatsoever. So basically, it's the same as attach/detach but instead of detaching the DB I took the Db offline and copied the files over. So my question...

Is this supported?
Any chance of corruption on either of the servers?
If a DB is offline, is copying datafiles and logfiles supported (I am bit worried if I might corrupt data in production).
Do people use this method to COPY databases or do they stick with BACKUP and RESTORE?

PS: Are there any other ways to do this?

Thanks so much.

View 5 Replies


ADVERTISEMENT

Copying DB On Same Server

Feb 16, 2006

Just installed Sql Server Express, trying to figure out how to copy a database. I'm pretty new to databases in general.

Let's say I've created a new database called test1. Now, I want to create an identical copy (data not important, I just need the table structures, keys, constraints, etc.) called test2. Not sure how I would go about doing that. I see that import/export functionality is disabled in the Express version, if that's what I would have needed. I tried copying the mdf file manually and calling "attach" - that failed. Any suggestions? Thanks!

View 2 Replies View Related

SQL Server 2005: Copying Tables And Stored Procedures Between Databases On Same Server

Mar 5, 2008

This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn

View 9 Replies View Related

Problem Copying Database From Sql Server 2000 To Sql Server 2005

May 15, 2006

Hi,

I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.

Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.

What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?

What it is that I am missing?

Kindly help me.

Thanks, regards.

Shobha

View 5 Replies View Related

Copying One SQL Server Database Into Another

Oct 21, 2004

Hi, I'm trying to copy one SQL Server Database into another SQL Server DB within the same server. I learnt a bit about this copying, that it copies only the Database structure, the Tabels, constraints view stored procedures etc etc. Is there a tutorial where I can get clear instructions to do this. I just need the table structure without any data to be copied into this. Have even tried exploring creating and executing DTS packages but cudn't get much help with this. Any help wud be really appreciated.

Thanks

View 4 Replies View Related

Copying SQL Server Website To CD

Apr 10, 2005

Hi!
Not sure if this is the right place to post. I need to copy a website that I created on a CD. It is using SQL Server as the database and VB, ASP as front end. However, the problem is that I need to copy it in such a way so that it can be viewed from the CD itseld without needing MSDE or SQL Server. Need help.
Thanks.

View 3 Replies View Related

Copying Logins To A New Server

Aug 21, 2002

I have succeeded in reattaching the db to the new server, but the old logins do not work, as has been experienced by many people.

What follows was is a solution from an earlier post (last year) on this topic. I tried running the queries from Master in on the new server, and got the following error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'sysusers' with unique index 'sysusers'.
The statement has been terminated.

The sql queries I am using are:

insert sysxlogins(srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language)select srvid, sid, xstatus, xdate1, xdate2, name, password, dbid, language
from "oldServerName".master.dbo.sysxlogins
where name not in ('DISTRIBUTOR_ADMIN', 'REPL_PUBLISHER', 'REPL_SUBSCRIBER', 'TRACEUSER', 'SA')

AND

insert sysusers(uid, status, name, sid, roles, createdate, updatedate, altuid, password)
select uid, status, name, sid, roles, createdate, updatedate, altuid, password
from "oldServerName".master.dbo.sysusers
where name not in ('public', 'INFORMATION_SCHEMA', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader','db_datawriter', 'db_denydatareader', 'db_denydatawriter', 'guest', 'dbo')

The old server is set up as a linked server on the new server.

This suggests that the information is all in the new server. Any tips? TIA D. Lewis

View 2 Replies View Related

Copying 7.0 Databases To Different 7.0 Server

Apr 18, 2001

Sages,

I have been trying to do this using both DTS and BACKUP/RESTORE. With the former my logins and users get transferred but no one can login. It's as if they are not linked to each other.

With the latter there are no logins to link to the db users. In Sybase we used to bcp the master..syslogins table. Does this work with MSSQL 7.0? Any other suggestions? TIA.

View 5 Replies View Related

Copying A Database From One Server To Another

Apr 26, 2001

Hi,

Does anyone know how to copy a database from one server to another? I want to backup a production database and restore it to a different server (a test server) as a test.

I used a sample from the MCDBA study book and all I got was two copies of Northwind on the same server. Any idea what I did wrong? Seems like you should be able to backup on one server and restore on another. Here's the code I used:

BACKUP DATABASE Northwind
TO DISK = 'Sd-appsmssql7DataNwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'Sd-appsmssql7DataNwind.bak'
RESTORE DATABASE Northwind2
FROM DISK = 'Sd-appsmssql7DataNwind.bak'
WITH MOVE 'Northwind' TO 'Sd-sqlmssql7DataNorthwind.mdf',
MOVE 'NorthwindLog1' TO 'Sd-sqlmssql7DataNorthwindLog1.ldf'
GO

Thanks for your help.

View 5 Replies View Related

Need Help Copying DTS Packages To Different Server

Sep 20, 1999

I have 5 DTS packages that I need to copy to another server. Can someone
please tell me how to do this.

I am a 6.5 DBA and now trying to adjust to 7.0. Big Change.


Thanks for any help.

Dianne

View 2 Replies View Related

"Copying" Jobs From One Server To Another

Aug 26, 1999

Hello -
I was curious if there is any way to "copy" a SQL Job, alert etc. from one server to another. I want to test jobs on a non production server before putting them out on our live Server. I understand you can do the whole Master server thing, but this temp server is often rebuilt for whatever we need.
Any insights would be greatly appreciated...
Thanks in Advance.
Joe

View 1 Replies View Related

Copying Databases To New Server

Oct 10, 2002

My production server has 8 databases connected to SAN where all the data resides.
This also has the system databases. There are two internal drives where I store the log files

We will be buying additional internal drives for the server and want to move all data to these drives
and free up SAN for other use.

Do I need to start building this server from scratch or is there an easy way to do this.
I can use sp_attach_db and sp_detach_db too but I prefer backup and restore
Is this the best option:
backup all the databases
uninstall sql server (since system databases are on SAN)
Reinstall sql server
Restore eveything on these new drives

Any ideas will be greatly appreciated

View 1 Replies View Related

Copying Records From One Server To Another

Jul 7, 1999

I need to copy records in a table on one server to a similar table on a diferent server. The table definitions are the same but not the data.

I have in mind to use the Transaction Coordinator and the copy should be done within a stored procedure.

View 2 Replies View Related

Copying SQL Server Table...

Oct 22, 1998

I`ve create a table which has 10 fields...Now I have to create 12 more tables which has same fields...
How can i duplicate or copy a table ?

View 2 Replies View Related

Copying SQL Database From One Server To Other

Nov 23, 2004

Hi All,

I need to take a copy of a database present in a SQL server to other SQl server using VB.net code.
I can make it out with wizards but i need the query to execute it thru vb.net.

Thanks
Aravind....

View 2 Replies View Related

Copying Tables In SQL SERVER

Aug 30, 2004

I have a 100m row table that I need to come from one database to another database in SQL SERVER.

The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.

Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)

and on a 100m row table it is taking around 52 hours. Not acceptable.

View 6 Replies View Related

Copying Database From One Server To Another

Sep 30, 2004

We recently got a new SQL Server 2000. I'm not really a SQL/Network admin but I was tasked to migrate some of our databases in the SQLSVR7 to SQLSVR2K.

I tried using DTS EXPORT but getting errors. Is there a better way to do this?

Any info would be appreciated.

View 6 Replies View Related

Copying A SQL Server Database

Mar 5, 2004

Hi

I am new to SQL Server. I am required to make a copy of a live database, or bring the database down and then make a copy of it.

I need to create a webpage to query the database, but another scheduling program also connects to the database, so that shceduling can be performed

Any help

View 2 Replies View Related

Copying Server Objects

Sep 30, 2005

I want to copy a database from one server to another. I'm happy abouthow to do this but also want to copy a number of DTS packages, jobs andalerts that relate to this database. Is there any way that I can copythem or will I need to create them again on the new server.Many ThanksLaurence Breeze

View 2 Replies View Related

Copying A Database To Another Server

Feb 8, 2006

Im running SQL Server 2005 Express with SQL Server Management studio installed on my laptop. After updating my database, how do I copy the entire contents to the main server in the office, which is running windows server 2003 and SQL Server 2000 Developer Edition.
And is it possible to do it all from my laptop?

View 1 Replies View Related

Copying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?

May 8, 2006

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

View 5 Replies View Related

Copying My Existing Database To Another Server

Oct 19, 2006

Hi I am running my sql 2000 database server  and I just want to copy that database and paste or put on another server which has also run same version of sql 2000 database. I tried to copy and paste the ldf & mdf extensioned files which are located in "C:Program FilesMicrosoft SQL ServerMSSQLData" into my 2 server, but when I open up the enterprise manager, I cant see the new database. So this way dont work, I tried to do something with export and import wizard but it just creates another database and copies the data in that database in the same server. I want to copy that data and put onto another system, please guide me. I'm confused, Thanks a lot

View 5 Replies View Related

Copying Server Jobs From Two Different Machines!

Feb 6, 2002

I am trying to copy DTS Packages and Jobs from two different Servers to one new server. I know I can script the jobs, but they won't run without the DTS packages. I have backed up and restored the MSDB database from Server 1 to my Main server and therefore I have all the jobs from that machine. My problem is how do I get the jobs stored in SERVER 2's MSDB over to my main server without replacing the tables I already have thus losing the jobs and packages that I imported? I have thought about backing up Server 2's MSDB then importing to another database,on the MAIN SERVER and then importing with an append to the 'live" msdb, but I believe the table names are the same and I may end up with duplicate entires. I don't know what problem this will cause. Any suggestions will be great. Thanks in advance

View 1 Replies View Related

Copying An Existing Database To A New Server?

Nov 8, 2000

I would like to create a test environment on a separate server running advanced server 2000. The current production server is NT4.0. What is the easiest way to copy the databases from production to test. I installed SQL7.0 on the new box then created the backup devices. Next I copied the backup files for all of the databases including master etc. to the new backup device folders. I tried to restore the master db first - then things went down hill. Any recommended procedures regarding this? Any help would be greatly appreciated.

View 1 Replies View Related

Copying Database To Hosting Server

May 20, 2003

I'm new to database development and have created a database-driven website which now needs to be uploaded to the client's hosting company server. What is the easiest way for a newbie to upload a copy of the databse to the host. Thanks in advance.

View 1 Replies View Related

Copying Data From Another DB On Server Problem

Mar 2, 2005

What iam trying to do is put the table name from one db into a variable and another one into another variable and pass them into my statement basicaly trying to bulk copy data from one table in a db and insert it into another db on the same server based on a condition

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20)
set @BDFR = 'Commander' + '.dbo.' + 'ClientComment'
set @BDTO = 'Test_Commander' + '.dbo.' + 'ClientComment'
set @EQID = '80_300_005'
insert into @BDTO
select * from @BDFR where Eqid = @EQID

View 2 Replies View Related

Copying SQL Server Express Database

Jun 5, 2006

Help!

I have a customer who I have set up a SQL Server Express database for. The database was running fine, but I copied it over to my pc over the weekend to update some data. I have copied the data backwards and forwards plenty of times, but this time when I came to copy it back and reattach it on the customers' server I get the following message:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Attach database failed for Server 'SBSSERVERBKUPEXEC'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Could not find row in sysindexes for database ID 6, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'ACServe'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)


This is REALLY urgent.

Help me please

Gabby

View 1 Replies View Related

Copying A Sql Server Database File

Mar 12, 2008

I am developing a .NET 2.0 application that uses Sql Express as its backend.
I use OleDb Provider to connect to Sql express.

One of the options in the app allows the Admin to create a Backup of the database. As part of this process, I write a log entry to one of the tables in the database, commit & close the connection, and then copy the physical database(.mdf) file to some pre-determined location using the following instruction (in VB):
My.Computer.FileSystem.CopyFile("Path to Database File", "Destination Path")

It throws an exception saying that the file is in use. Upon checking, I found that the file has been locked-up by the Sql process.
I ensured that my app closes all connections to the database, that there is none other trying to connect to it, and then again tried to execute the above instruction in my app, again raising the same error.

However, when the app terminates, then I can easily copy the databse using explorer. I also used the OleDb method ReleaseObjectPool(), but the problem persisted.

So, how can I copy the database in such a situation from within my app??

View 3 Replies View Related

Copying Procedures In SQL From One Server To Another Using VBA In Excel

Sep 25, 2007

Is there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...

View 4 Replies View Related

Copying Data From Microsoft Access To SQL Server

Oct 16, 2006

Hello I am developing a web application that will allow users to upload a .mdb file and from that file I need to populate an SQL database. I know the table name of the .mdb file, but I am unclear how to structure my data access layer correctly. How do I pull data from the .mdb file and once I have it how do i populate the SQL database?Any advice would be greatly appreciated.thanks! 

View 1 Replies View Related

Copying Database From Server To My Local Machine.

Feb 27, 2006

First of all let me explain that I am a complete newbie to this SQL stuff. I am in the process of reading a book, and followed the books suggestions for what I want to do, but it doesn't work. I know this has been covered several times in this forum, but no one post in particular covered exactly what I want to do.

I want to copy the database from the server to my local machine.

First, do I need to have SQL running on my machine in order to copy the database? I do have Enterprise Manager running, does that mean SQL Server is running locally?

Secoond, I tried using the Copy Wizard, but when I get to the target list, my local machine is not listed. How do I get it listed so I can copy the database?

I hope my questions are not too stupid... but then I'd be stupid if I didn't ask questions.

Thanks in advance for your help...
David

View 2 Replies View Related

Copying Table From Local To Linked Server

Feb 17, 2004

Hi,

I'm trying to copy all the data from a single table across to a remote linked server and I'm having all sorts of problems.

The SQL query I'm attampting is this:-


Code:


EXEC sp_addlinkedserver 'GODZILLA', N'SQL Server'
EXEC sp_addlinkedsrvlogin 'GODZILLA', 'False', 'rmtUser', 'lclUser', 'password'
Go

--DELETE TABLE
DROP TABLE GODZILLA.Racing.dbo.horse

--SELECT INTO
SELECT horse_id, horse_filename, horse_time, horse_new, horse_driveLetter
INTO GODZILLA.Racing.dbo.horse

FROM Racing.dbo.horse

EXEC sp_droplinkedsrvlogin 'GODZILLA', 'lclUser'
EXEC sp_dropserver 'GODZILLA'




Basically - this doesn't work as an error is returned:-


Code:


The object name 'GODZILLA.Racing.dbo.' contains more than the maximum number of prefixes. The maximum is 2.



Now - it has no problem running a SELECT (ie read-only) query from the remote DB but why does it have a problem with writing? And if thats the issue, why doesn't it say so. Am I going about this the right way???

Please help!

View 3 Replies View Related

Issues Copying Data To Linked Server

Oct 24, 2005

I'm having an issue with a job that calls a stored procedure. The SP is fairly complicated, but basically creates a temp table, then inserts the records from the temp table into a table on a linked server (via VPN). The basic premise here is that I'm trying to update a web-accessible server from the production server. The funny thing is that the process works correctly for 4 out of 5 companies (company is the only parameter fed to the SP). The company that fails is by far the largest, but the process has worked in the past.

I'm not strong with all the tools available in SQL Server that might help me figure out what's going wrong. In the SQL Server Agent, it simply tells me that the job failed. If I use Query Analyzer and run the SP in Debug mode (if I'm stating that correctly), I get this error for any company I try it for, even companies that run fine (line 51 is the DELETE line):

Server: Msg 7391, Level 16, State 1, Procedure procStmtDataToReno, Line 51
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

So I suppose my long-winded question is, how can I determine why this SP fails for the one company? If relevant, the job is simply:

EXEC procStmtDataToReno 'B'

and some snippets of the SP:

CREATE PROCEDURE [dbo].[procStmtDataToReno]

@CoCode as varchar(2)

AS

--various declarations and such

DELETE
FROM [10.1.1.4].OnlineAR.dbo.StatementData
WHERE CompanyID = @CoCode AND (Line1Date < Convert(varchar(10), GETDATE() - 60, 101) OR Line1Date >= Convert(varchar(10), GETDATE() - 5, 101) )

--code to build temp table

INSERT INTO [10.1.1.4].OnlineAR.dbo.StatementData
SELECT * FROM #Statement
GO

TIA

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved