How to import db data from the old mdb file to the new database?
-
I created a new database and created tables and procedures from sql file from existing db.
New db has its own mdb and log files. Later I was given some existing databases' mdb and log files and I was asked to populate my new database with this data.
How can I replace my mdb file with the old mdb file that has data? I tried to rename them, swap their names but it didn't allow me to do so because my mdb file is in use. Is it possible just to add one more mdb file to the database?
The purpose is to import that data into the database from the mdb file but I don't know how to do it.
I was also given mdb and log files for ASPNETDB database. I created it by running a command from the command line. I even do not know where's mdb and log files are located for ASPNETDB and I do not know how to replace them with the old ones.
-
Backup and Restore the old database SQL Server will recreate everything and you can change the name as needed during the restore, one more thing there is no mdb file in SQL Server. SQL Server uses mdf.ldf and the secondary files. Hope this helps.
Kind regards,
Gift Peddie -
you are right mdf, idf. I back up database and log twice a day automatically though this db is empty. I will try to restore it from the old files. Hopefully I will not screw anything. Still I don't know how to restore data in ASPNETDB from the old files. Thanks for the advice!
-
If your ASPNETDB is located in the AppData folder you may run into problems retoring it to the IIS folder, if that happens you may look into restoring in Network drive or create a blank database and copy the data from the old database to the new one. Hope this helps.
Kind regards,
Gift Peddie -
"Backup and Restore the old database SQL Server will recreate everything and you can change the name as needed during the restore,
one more thing there is no mdb file in SQL Server."
Doesn't work.I have completely clean new database and I have a backup for that empty database. Then I have two files old.mdf and old.ldf from the old database that were given to me. But I do not have old database and I cannot make its backup and restore a new database from it. It's not like MySQL where one can dump all the database into the text file with the table structure and insert statements for data in and keys and indexes. It's Microsoft. Everything should be as hard as possible.
When I try to restore a new database in the Management Studio, it doesn't allow me to use those old.mdf and old.ldf files. It wants me to restore from the current db backup files that are in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\new_database.bak
So there is no way to get data from old.mdf into the new.mdf .
-
Sorry I misunderstand you all you need is to attach the old database in the AppData folder, try the link below for two ways to do it either through Management Studio or with code. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms187858.aspx
Kind regards,
Gift Peddie -
I did it, I did it!
Exec sp_detach_db @dbname='new_db'
EXEC sp_attach_single_file_db @dbname = 'new_db',
@physname = 'c:\data\old.mdf'
-
I glad you have solved your problem you see it was not complicated.
Kind regards,
Gift Peddie -
Hi Gift Peddie,
I didn't solve it, I made it worse. So I didn'' sleep last weekend because I worried so much about it. I am not a dba, I am not a .NET developer, I am an open sorce developer, linux/apache/mySQL/PHP/Perl. And I just started a new job and it's MS/IIS/SQL 2005/VB.NET. No time to study, I was given somebody's else database to install and somebody's else scripts to work on. So I am worried about my job very much and don't have enough knowledge to make it work.
Last week I created a database, mdf and ldf files, run script to recreated tables, scheduled db backup in the management studio and it was all right. Then I put VB scripts into wwwroot directory, run web application, got an error, cannot connect to the database. Then I created server login and database user, run web ap again, got an error again. User doesn't have permission to select. So in the login I set user as datareader/datawriter/public and in the database user I set schema as datareader/datawriter and role members also datareader/datawriter. Then I run that application again and I saw normal page, no errors. So I was very happy.
But then database was completely empty and I needed to populate it with existing data. This is when I came to this board to ask a question. So I deattached my database from my existing mdf file and attached it again to the new one. After that I got an error Cannot open database, login failed for my user. No matter what I did to that user, nothin helped. I tried to delete db user and create a new one, it didn't allow me to do that because user had existing schema.
I was thinking about dropping whole database and start all over again, but then I heard that after dropping and creating again a database, people automatically get the same user back, you cannot get rid of the user.
Now I am stuck and my project manager asks me all the time when they can test the ap. It's very frustraiting.
-
There are two permissions in SQL Server the server permissions under security and the database permissions in the security section within the database, if you are in Windows 2003 you need to create permissions for Network Service account in both places, if you are in WinXP you need to create the permissions for Asp.net account you created. You also need to give the account permissions to execute stored procs and create objects. Hope this helps.
Kind regards,
Gift Peddie -
OK, I am too stupid to understand it
"create the permissions for Asp.net account you created". - what does it mean, create server login?
It's not permissions, it's login failed.
It's WinSP, SQL Server 2005 with Management Studio. In the Management Studio there is server/security/logins and I have login with the user name there; and database/security/login and I have same user with permissions there.
I will try another thing. Just create a new clean database, new user, and then populate that database from the old database using DTS packages without deattach/attach
Maybe this will work? Thank you very much for all your help. It's very sad when I have nobody to ask at work or anywhere else.
-
In XP you have to create Aspnet user and create both logins and permissions for that account in the database. You still need to create the permissions and login in the new database.
("create the permissions for Asp.net account you created". )
I mean create Aspnet user account in XP and add that to SQL Server and the database. Hope this helps.
Kind regards,
Gift Peddie -
Hi Vikas,
Here is a walkthrough for the correct configurations for the Asp.net Membership database this is assuming you are running SQL Server 2005, there is minor difference for 2000. Hope this helps.
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
Kind regards,
Gift Peddie -
Hi Gift Peddie
Thanks you so much for the info.What does Peddie mean?
It was funny with that database yesterday. I created a new database, login/user/permissions, application can see it but it's empty. Then I compared new database and the old one that I deattached and attached. Table structure was different! The guy gave me newest version of db from the dev server and old data files from the production server. So old databse got corrupted somehow. Very funny.
OK, back to the microsoft
-Vika
-
It is not complicated the confusion is from using existing code I am going through the same thing in a very big application, it will become rudimentary when you get to know how things work there. Peddie is my father's name.
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply