Hi,
We have a 2 node cluster and want to move our database files from the F: Drive to the G: Drive.
The user databases are easy enough (detach/reattach).
Can anyone give any ideas how to move the system databases?
Thanks
The area to change my default database location is "greyed" out. I wish to put all my data on a seperate drive than the OS (D:). What am I missing that I can't change the default database location?
View 1 Replies View RelatedHi,
We have a new failover cluster (Windows 2003 SP1, Microsoft SQL 2000 SP4) with each node of the cluster hosting 7 SQL Server instances in a 2-node active-active configuration connected to a SAN. We are planning to move some SQL Server Instances(from existing stand-alone servers) into this Cluster. Any insight into the process of moving SQL Servers into the cluster would be highly appreciated.
Best Regards,
Himansu
On a SQL 2005 Cluster, when you move the SQL resources you generally have to kind of "reauthenticate". For example, in SSMS and after you have moved the SQL cluster resources, you run a query in SSMS and you get a connection failure. Run it again and it works.
Does anyone else notice this? I am assuming it is because the resources are now on a differen't server. But then what's the purpose of the SQL virtual server name and instance? I thought when you authenticate to SQL it's to the virtual SQL server like so... VirtualServerSQLInstance.
Can someone straighten out my poor mangled mind?
I'd like to avoid this reauthentication thing.
Thanks
Hello there,
I've some really big problems with SQL Server 2005 Express. I Recently, I had 2 instances on my machine, one was a SQL Server 2000 Developer and the other a 2005 Express Version. The 2000 version was not necessary anymore, so I unstalled it, since then, the Express version keeps having probems.
Under the Server Properties -> Database settings -> Database Locations I've changed the path to D:sqldatamssql, but now, the programm always takes the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" to store and load the databases, no matter what I do.
And there are more problems see here:
Restoring does not work=======================
I wanted to restore a database called "fw40_admin" from a backup file (.bak), but that didn't work at all, it always says this as an error:
"System.Data.SqlClient.SqlError: The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:sqldatamssqlfw40_admin.mdf'. (Microsoft.SqlServer.Express.Smo)"
But as soon as I type in the installation path "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" inside the "Restore As" under "Options", it works.
Why does the backup only work in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData", BUT the database locations under the server properties is indicated with "D:sqldatamssql"????
Attaching does not work=======================
Attaching an existing database does not work either. Most databases (mdf) are located on the path D:sqldatamssql, but as soon as I press
Attach Databases -> Add
The dialog window appears with the "D:sqldatamssql" path, but it is unable to find any *.mdfs altough THERE ARE .mdfs in this directory. But Express can find databases in the selected installation: "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData".
Why can't Express see the directory on the D: partition???
What is wrong? Here? Can anybody help me?
Hi
We are planning to upgrade the SQL Server in our production environment from SQL Server 2000 to SQL Server 2005. This is a 4 Node cluster environment with 3 Databases on 3 Virtual instances. The main requirement is to achieve this with no/minimal downtime.
Could you please suggest or direct me to any documentation for the best practices used to upgrade such an environment?
Thanks
Priyanka
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
Can oracle91 and MS SQL2005 be on same server
View 20 Replies View Relatedhey i am going to be writing a query to search for stock in a certian location, but what i wnat to do is allow the user to search in all locations as well, how could i do this? or would it be more asp based?
Jez
I know I've seen documentation on this but I can't find it at the time. What's the recommended file locations for a SQL install.. System and Data on a RAID drive and logs on a separate drive that's mirrored..? Oh and if anyone has links to this info let me know also.
Thx!
I am a newbie in SQL Server Administration. I decided to make the move within my company that no one else wanted.
I have SQL Server 2000 running at this time; users are connecting and using it. My question I guess pertains to the backups.
I have two drives setup for data and transactions logs (D: E:)
My backups are running but backups are being sent to the d:systems datamssqlackup folder. once backups are comlete I normally copy the backups to a network share that is being backed up nightly to tape.
My question is this:
How do i create a device location under backup properties to point to a network share? i would like for the backups to go straight to a network drive so i do not have to copy the backups every morning when i come in.
I am trying to split the log and data files between two drives on a newSQL-Server 2000 installation. I followed the instructions from the MSarticle 224071 (Moving SQL Server databases to a new location withDetach/Attach).Unfortunalty, when I try to move the Master database, as perinstructions, access to SQL-Sever is lost;the service will never startagain. I've tried this twice after re-installing with the same results.Anyone have any ideas as to what is going wrong?Tim
View 3 Replies View RelatedHello there,
I need to install SQL 2005 in one drive on my computer, and data folders in another drive. Can anyone tell me how this can b done during setup please?
Thanks in advance.
Hemant
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?
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
Hi, Everyone,
I need to copy a Database onto another Server, what is the best way to do it?
Thanks
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
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 RelatedI 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.
Hello
I have a table with inventory where each item has a field called location. What i would like to do is to get a summry with how many items there are in in each location. All the data is stored in one single table named article. inside this table i have Counter (PK), Artnr, location, type and some other things. 2 other importent columns are date and interval. Date is the last time the item was inspected and interval is an integer saying how many months untill the next inspection. So i guess Counter, location, date and interval, are the ones i need to use in my query to do the following.
Wo what i would like to do is to present a short list with all locations and then a number that says how many items need inspection in 3 months or less (and even items that have apssed their date). Im somewhat new to sql so iv only been doing the regular insert, delete and update here and there but i have a feeling this query is a bit more complicated. The list does not need to say what items needs inspection. I only need to know how many.
Could anyone help me out with constructing this query?
I have a server that is being rebuilt soon and the data storage is going to be be restructured at the same time.
Currently all data, logs and backups are held in the default e:mssql7 directory (on a RAID 1+0 system).
After restructure data will be one one drive (RAID 5), backups logs and templdb will be on various others.
I have a couple of questions;
1. My plan is to detach all databases and backup the .ldf and .ndf files (logs are not as important after this point). Then backup master and msdb databases. Once the new server is built (specifying the new file locations at install), I will restore master and msdb and finally reattach the databases from their new locations. Does this sound reasonable?
2. At the point of server install, I intend to specifiy the default locations for data and log files. When I come to reattach the data files I want SQL Server to create a new log, but place it in the new default location. Will it do this automatically?
Thanks in advance for your help
Jonathan
Just as an aside...
If you have a question or problem with SQL Server you might want to try three places before/in addition to listing questions here... only because it will be quicker for you rather than waiting for someone to post a response.
1) SQL Server Books OnLine (install on hard disk when you install SQL Server... full set of manuals and highly indexed)
2) http://www.microsoft.com/Support/ (you may need to register but it is free) (Use Support OnLine and look under SQL Server for specific support)
3) http://support.microsoft.com/support/default-faq.asp (FAQ)
Both can help with error numbers and #2 is especially good to find known bugs, fixes, work arounds, etc.
Hope this helps,
Don
Hi,
I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.
I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.
Many thanks in advance
Here is what I am trying to accomplish. I need to move *.pdf files from a local directory into a local staging directory, then from the Staging directory FTP them up to the customers site. Then move the files from the staging directory to an archive directory.
I can do this fine as long as all values are static in SSIS, I need help to figure out hoe to do this using variables. The Directories to be used are to be supplied by the DB all that is give in the directory itself all files in the directory need to be moved.
Any help would be appreciated.
Thank you,
Mike
Michael Alawneh, DBA
I'm using a SQL 2008 database on a SQL 2012 hosted server. I have an autoincrement ID for locations that my members create. These go up by 1.... eg. 9400, 9401, 9402.
Tonight a member attempted to make a new location and the ID went from 9407, 9408 and JUMPED to 10408. Any subsequent creations only continue from 10408 onwards.
What happened here? 9409 to 10407 just aren't available.
Hi,
I have to create a datebase for an assignment and I am using MSSQL and need a little bit of help for something that I don't think can actually be done.
Basically I have an artist table. Now each artist may well have a website of their own where their biography can be found. In my artist table I have an artist_bio field where I want the URL of the artists bio to be placed and only the URL. So for example I would want www.iamanartist.com/mybio to be put into that field. So I was wondering if there was a constraint that could do this?
I don't think there is because URLS can be so complicated but if someone could tell me whether it is or isn't possible I would be really grateful.
I am also wanting to do something similar with the location of a hardcopy of their work. So I have a digital_images table with a location field where the loaction of their work will be on a harddrive. Is this possible or not as well?
Cheers for any help you can give,
Liam
SQL Express is quite sizable for what I want to do but it appears to have many features that could turn out to be highly desirable.
It makes sense that the entire SQL Express must be installed at the developer's location, but what portion of it is required at the application user's location? All, or some?
Thanks
I have a maintenace plan that copies production backup files in DR server. The production is mission critical and runs 24/7. We have a round the clock backups. Backups include full backup, differential backups and log backups.
To secure these backup files, I was asked to keep these files in different server at the same time as it goes to DR Server. As I am using maintenace plan for scheduled backups that go to DR server and I had no idea how to incorparate copying these files to another server in the same maintenence plan. I came up with idea using xp_cmdshell stored proc that I attached on the maintenace plan in executeT-SQL statement task. The statement looks like
Exec master..xp_cmdshell 'copy e:DRbackup*.bak \fgh-sql16ackup$'
Above statement copy all backup files on DR server and takes these to remote server called fgh-sql16.
My question here is ....are there any other effective methods to copy backup files simultaneously to different servers via maintenace plan.
Thanks all
What is the recommendation for having multiple data files ? I've read 1 per CPU etc... some say keep it all in one data file.
How do multiple sql data files help when they are located in the same raid group/single volume ?
This is the setup that I have:
4 CPU box with 2x P400 array controller to 2x MSA10 exclosure (for data only, tempdb,logs,backups are on other arrays)
one array controller per enclosure. single big RAID 10 volume on each ( or should I do 4-4-2 separation).
(In single big raid 10, All disks would help for performance of each query... I don't want to create separate raid groups, mostly due to growth constraints in the future, am I wrong ?)
How should I split database across these two enclosures ? one,two data files on each or more ?
How would this look in OLAP vs OLTP scenarios ?
Thank you.
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.
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.
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.
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