Moving A Database

Nov 14, 2007

I have two computers, both with SQL Express 2005 and SQL Server Management Studio Express. I created a database on one computer, and need to copy it to the other computer. I'd like to do it all through the Management Studio interface, without using SQL commands, since I only need to do this manually.

I detached the two files, copied them to the second computer, and tried to attach them. This is generating lots of messages about not having the necessary permissions of various sorts. I think the main problem is that I don't know how to set up the database's users in a way that allows the second computer to attach the database.

I've tried Windows authentication, using a Windows user that has the same name and password on both computers, and I've defined that Windows user as a user within the database. I've connected to SQL when logged in as that user.
I've tried SQL authentication also, defining the same SQL user within both SQL Servers, as db_owner. But when I try to attach the database, I see no place to specify the user that I intend it to use, and it still fails. If I connect to SQL as that user, then SQL doesn't have permission to look at the file system to find the MDF file.

I'd appreciate the help in learning how to do this with both Windows authentication and SQL authentication, via the Management Studio's GUI. Most of the documentation I've seen covers how to do it by using SQL language commands, and I'd like to avoid that.

View 5 Replies


ADVERTISEMENT

After Moving 2000 Database To 2005 Database

Oct 15, 2007



I have used backup and restore method to upgrage 2000 databases to 2005 database.
What do i need to do in order to for my application or users to use databse in 2005 and remove 2000

let me know
thanjs

View 3 Replies View Related

Moving A Database

Apr 23, 2007

Hi
I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)
I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the source PC to the destination PC (Same folder).
I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?

View 7 Replies View Related

Moving An SQL 7.0 Database

Apr 9, 2001

I have an SQL database running on an old server and i am upgrading the server, but i am having some problems moving my SQL database over. Has any one got a work through they could mail me to give me a hand!!

Cheers

Mike

View 1 Replies View Related

Moving Database

May 29, 2001

Hi, Everyone,

I need to copy a Database onto another Server, what is the best way to do it?

Thanks

View 2 Replies View Related

Moving Database

Mar 28, 2001

Our vendor has created a sql server database at his location

We have to transfer this database to our location and run the web application which access this database.

What is the best way to do this.

Can the vendor zip database and sent to us.

What do we do with ZIP file.

Can somebody help

View 3 Replies View Related

Moving A Database

May 3, 2007

I'm very new to SQL Server 2005, so please be kind.We had a vendor in and they loaded their software and the databasethat was created is sittingon the root (c:) drive. On this server we have another drive wherethe database should reside, isthere a way to move this database to the correct drive? Thank you forany help that you couldprovide.Dave Schaeffer

View 1 Replies View Related

Moving Data From One Database To Another

Dec 5, 2006

Hi all,
I want to transfer all the data from one of my Ms Access tables to SQL Server table, using C#.
How can i do it?
Thanks in advance,
J.Jasmeeta.

View 6 Replies View Related

Moving Data From One Database To Another

Feb 2, 2007

Hi all,
                        I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates.
                       Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table.
My Ms access table
Name        Id
jas            100
meena       101
viji             102
 
My Ms sql table
Name        Id
jas            100
meena       101
viji             102
 
After 1 min, say  2 records are added to my Ms access table like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
Now i want to insert only the latest records from Ms access to Ms sql like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
how to do this? thanx in advance.
Jasmeeta. 

View 4 Replies View Related

Moving A Database To A Server

Jul 11, 2007

I have a SQL Server 2005 Express database on my local machince called OpenAssess.mdf. The server we host with has a file extension of .mdb. How can I go about getting my database to the server? I tried changing the extension to mdb on the local machine but then it tried opening the database in MS Access and didn't work. I just need to connect to the database in my web pages. Here is my connection string and then the error which is visible at the botton of openassessment.org.
*********************************************connection string*************************************************
OPEN_Conn = "Provider=SQLOLEDB;Data Source=connectionToHostServer;Network Library=DBMSSOCN;Initial Catalog=OpenAssess.mdb;User ID=myuserid;Password=mypassword"
*****************************************************error*********************************************************
Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database requested in login 'OpenAssess.mdb'. Login fails.

View 1 Replies View Related

Moving Database Roles

Nov 12, 2001

On 11/10/01 I posted a question on how to move passwords from one sql2000 box to another. The response to this question worked perfectly.

I also need to copy over the user or database access information from one box to another. I tried to use dts but it only copied over the users and the database roles but did not copy over the Database user properties which allows the user access and places the user into security groups.

Any help would be appreciated.

Thanks again.

Steve

View 1 Replies View Related

Moving Tempdb Database .

Aug 14, 2002

Hello ,

I want to move my tempdb database to another drive by moving the mdf and ldf files . Is it possible to shift the tempdb once it is created ?
I am shifting the database since there is no space on the current drive and the database might grow in the near furure .

Any ideas ???

Thanks .

View 1 Replies View Related

Moving Database Directories..

Oct 16, 2000

I have a machine where the SQL "data" directory was installed on the smaller drive instead of the larger drive. What do I have to do to move the existing databases? (Backup/restore, or detach - move it physically, then attach?)

Thanks, for the help

View 3 Replies View Related

Moving The MODEL Database

Sep 27, 2000

How can I move the Model database to another location (i.e. from one drive / file location to another)?

I tried sp_detach_db (to be followed up with a sp_attach_db statement) but it rejected it because Model is a "system" database.
I tried (and it failed with a "Modify file command failed" message):
ALTER DATABASE model
modify file
(NAME='modeldev',FILENAME='f:MSSQL7DATAmodel.MDF')

I could not locate any help about moving model in books online (BOL).
Any ideas?

View 2 Replies View Related

Moving Database To New Server

Oct 16, 2006

hi guys

i got few questions. i need to move a databases from old server to new server. old server has 25 user databases total datafile around 300GB. heres the questions.

1. what is the fastest way to move all the data to new server? and what's the best way to do it? (DTS? MDF copy over/attach? bak copy over/restore? other theres other way?)

2. i got alot of job and user need to transfer over. i know DTS can do the user.. how about the jobs?

3. do i need to move the master and msdb over too?

4. beside all 3 questions above... do i miss anything that need to be move too?


Thanks guys!!!!!!!!

View 14 Replies View Related

Moving A Database To A New Server

Jul 15, 1998

Hi,
I have a database that resides on a server that`s become too old. It spans
2 devices that sit on 2 different drive letters - D: and E:.
I bought a new server, installed and configured NT4.0 and SQL6.5.
Initially I restored the database to the new server from a dump .It worked
fine, however all the login information was lost. Then I tried partitioning
the new server the same way as the old one and copying all the DAT files
over. Thus I was hoping to retain the information contained in the MASTER.DAT,
drop the database, repartition to create 1 physical drivewith 1 database device,
and restore from a dump to it.
After copying the DATs, though, SQL Server would not start.
Help Please!

View 2 Replies View Related

Moving Database To New Server

Dec 1, 1998

I am not a SQL person. I want to copy/move a SQL datbase from old server to a new server and eventualy bring the old server down. What is the procedure to do it. Thanks in advance.

Navid

View 1 Replies View Related

Moving A 2000 Database To 7.0

Aug 9, 2001

We have developed our database in 2000. We want to run this DB on 7.0 But we get a bunch of errors. (we are not using any 2000 specific features).

Can anyone tell me way to export a database on 2000 to 7.0 ?

View 1 Replies View Related

Moving A Database From Old To New Server?

Jun 28, 2004

Hi all. I know that since you know nothing about my database it will
be a stretch to answer this - but does anyone have a boiler plate
checklist for moving a database, dts packages, agent jobs, etc...from
one server to another? Maybe something that just says I should at
least do x,y,z.

Also if anyone out there has any hard won experience/advice about
gotchas when doing this I would love to hear it. We have outgrown a
server and are installing a new one this week that we need to move
over to.

Thanks!

View 1 Replies View Related

Moving System Database

Jan 26, 2005

What is the correct order:

Should it be:

Master
Model
MSDB
Tempdb

I understand that the model should be before the MSDB database but as for master and tempdb I don't know the order.

Thanks

Lystra

View 10 Replies View Related

Moving SQL SERVER Database To Other HDD

May 8, 2008

Sql Server 2000 is running on machine havivng two HDD. The HDD where SQL Server resides is reaching capacity. Only 3Gb is remaining. I want to move SQL Server database to other hard drive so that space problem would not occure. How to do it?

View 1 Replies View Related

Moving Database To Another Computer

Dec 2, 2005

hi all,

I'm having real trouble trying to place another copy of my database on a different computer.

I have developed a database on my main PC. But I want another copy of this same database to reside on my laptop so that I can do work on the move.

So far no luck. I installed SQL Server on the laptop but it won't let me create a database based on an existing database file.

Any ideas guys?

cheers,
token

View 4 Replies View Related

Moving Database To New Server

Dec 20, 2005

i need help moving a database to a new server.

I have an existing database on a Windows XP Pro box running MS SQL Server Desktop Engine 8.00.

I want to move this database to another machine running Windows Server 2003 Small Business Server w/full SQL Server (new edition).

I can see the database, and the directory structures are almost identical on both machines. But when I simply copy the data over nothing works. Obviously to me I must somehow back up the original database and restore it somehow on the new machine.

Any tips on where I begin? I have done searches in the Microsoft Knowledgebase for articles on how to backup and restore from one type SQL to another with frustrating results. No clear directions.

Thanks!

View 3 Replies View Related

Moving Database From One Machine To Another

Jan 15, 2007

I'm a beginner user. I'd like to move the whole database from a work station to a server, how should I do it in a safe way? First of all, I have a very basic question: How could I save a database in SQL Server? Thanks in advance.

View 3 Replies View Related

Moving Distribution Database - How?

Aug 8, 2007

Hello,

I have my distribution database at C drive, I have to move this database to another drive D.

May anybody tell me how do this secure, please.

This production environment so I can't make any mistakes.

--
Regards

View 4 Replies View Related

Moving Database Files

Aug 28, 2007

Sorry for the ultra-beginner question, but I've just recently started playing with SQL 2005 Express Edition for a task I've been assigned to at work.

I'm building an ASP.NET 2.0 web site that needs to connect to a SQL database to pull information. I installed SQL Express 2005 and installed the SQL Server Management Studio and I've managed to build a small database with one table.

I noticed that by default, the mdf and ldf files are located in C:Program FilesMS SQL ServerMSSQLDATA (or something along those lines). The website I'm working on is temporarily stored in C:WebSite. I need to move the database files over to C:WebSiteApp_Data so I can access them easily with ASP.NET and VS 2005 Express.

I can copy and paste the mdf and ldf files, but then I can't figure out how to point the SQL Server Management Studio to the new location.

I am obviously just a complete newbie at all of this. It's pretty sad that I can't figure out how to simply move the database from one directory to another on the same server... it makes me worry about when I'll need to move it to the new webserver!

Could anyone point me in the right direction at least?

Thanks in advance!

View 2 Replies View Related

Moving The Distribution Database

Aug 3, 2005

Hi all,Is it possible to move the distribution database to a new folder/drivewithout removing replication? I am attempting to do it the same way youwould move tempdb:ALTER DATABASE distribution MODIFY FILE(name = distmodel, filename = 'C:DISTMOVEDdistribution.MDF')ALTER DATABASE distribution MODIFY FILE(name = distmodel_log, filename = 'C:DISTMOVEDdistribution.LDF')But I get -Server: Msg 5037, Level 16, State 1, Line 1MODIFY FILE failed. Do not specify physical name.Thanks,Josh

View 2 Replies View Related

MSDE && Database Moving.

Jul 20, 2005

Hi,How to copy database diagram (all data) to other server using only MSDEserver and VS.Net Proffesional? I have no Enterprice manager. I have tomove database (create on my machine) to my customer server. Is somepossibilities?--*Pozdrawiam,*Klaudiusz Bryja

View 2 Replies View Related

Moving Database To Different Drive

Dec 7, 2007

Hello all -

I have TFS installed on one machine, and the SQL Server database on another. I made the unfortunate mistake of installing SQL Server to the OS drive (C : ), so the TFS database is writing to this drive. How can I switch this to write to another drive (e.g. E:)?

thanks

View 6 Replies View Related

Moving Database To MSDE 2000

Dec 20, 2003

I have detached my database from the server and copied the mdf and ldf files to the new PC do I need to do anything but attach it to the MSDE? What about logins, roles, etc... does all this remain the same or do I need to delete the old and reenter them?

Please provide any help, thank you.

View 1 Replies View Related

Moving A Database To A New Path On Same Machine

Mar 20, 2001

I am new to MS SQL 7.0, I like to know what is the best way to move the location of a database to a new path on the same machine.

I have mostly MS SQL 7.0 sp3, only one MS SQL 6.5 with sp5.

View 1 Replies View Related

Error In Moving Database To Another Server

Jun 20, 2001

Hi

I have to copy (move) a database from one server to another .
One of the way of doing it is to take backup at source server and restore it at destination server .

I am doing it with the following series of statements .

================
At Source Server
================

USE master

EXEC sp_addumpdevice 'disk', 'DBName_Device',
'Irhahadb01F$mssqlackupDBName_Device.dat'

BACKUP DATABASE DBName TO DBName_device


=====================
At Destination Server
=====================

--Copy file (DBName_Device.Dat) from Source backup folder to Destination Backup folder

USE master
EXEC sp_addumpdevice 'disk', 'DBName_Device',
'Irhadvdb02D$mssql7ackupDBName3_Device.dat'

RESTORE DATABASE DBName
FROM DBName_Device
WITH RECOVERY,
MOVE '<Data File Name without .mdf>' TO
'irhadvdb02D$mssql7data<New Data File Name>.mdf' ,
MOVE '<Log File name without .ldf>' TO
'irhadvdb02D$mssql7data<New Log File Name>.ldf'


Above step works fine for one of the test database which small in size.

But it fails for the large database having Data file (.mdf) as 2.5GB and log file as 1.5GB .It says " not enough space on network drive to restore database" . But fact is that I have 200 GB free space on the server .

Please advice .

Sujit
860 520 7454

View 2 Replies View Related

Moving System Database Files

Sep 19, 2002

Is this possible? If so, how is it done?

View 1 Replies View Related







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