Copy MS SQL SB

dman

dman

Perch
Copy MS SQL DB

Hello,

I'm yet another green MS SQL user and I'm having some issues trying to copy a database at Jodo. Basically I want to copy a current DB, rename it and customize it for a duplicate use. Can I do this myself?

I have SQL 2005 Developer Edition but I'm on MSSQL9 which I believe is 2000. I have downloaded a .BAK file using Jodo's backup tool at http://mssql9.xxx.com/mssql/. I used this to modify the file locally but I can't seem to restore it using Sequel Server Management Studio. I can however import the data but it looses the primary keys, triggers, etc in the DB at Jodo. I tried using Jodo's tool to restore the .BAK file but it fails and says it is a 2005 file.

What is the best way to copy a DB? I tried the SMS copy function on MSSQL9 but it says it is only for copying to 2005.

How do I and can I transfer my local DB in 2005 using SMS to MSSQL9?

Any help is much appreciated!
 
Following up... I made a backup of the original DB on the Jodo SQL server and tried to restore and overwrite the new DB I am trying to copy to but it says the DB is currently in use. I am banging my head against the wall now ):||
 
Have you tried the import export feature in management studio
Right click database in object explorer
tasks
import/export
 
Hey cdog,

Thanks for the reply. Yes, I tried the import/export feature but it only imports the data and tables. All the primary keys, triggers, etc are lost. Is this normal?

I really just need to copy a DB that is already at Jodo and make some minor customizations to duplicate an application. I have recreated the DB locally and made the changes but I can only import the data and basic structure.

I've read thru the forums and searched in Google, and there is mention of DTS but I've got 2005 locally and MSSQL9 is 2000. I think there are some compatibility issues between the two.

Any other ideas? I submitted a ticket but the support response does not answer my questions. I tried the backup tool but it says it can't load a 2005 DB. I'll have Jodo try it but I think they will have the same issue.
 
DMAN
Yeh Ive encountered the compatability issues here and there but its not all that often.

When you did the export did you have the optimise for many tables check box ticked. If so try unticking it before you click the finish button. I have a vague recollection of that causing me similar issues when i first went to management studio from query analyser. Havent tried it from mssql2005 to 2000 though.

Maybe jodo could just move your database to a 2005 server



I' have quite a few local dbs still running on mssql2000 that also run on jodo mssql2005 and often move data between the two



I think SSIS has replaced DTS in mssql 2005
 
Thanks again for trying to help cdog. I just tried to import the data again and the data and structure is imported but the primary keys, etc are missing. I'm really in a bind here as I need to bring this development DB online ASAP.

I've got a support ticket in but they are having problems accessing the DB probably because I tried the "Take offline" in SMS from the tasks which always breaks the DB.

Any other ideas about why the primary keys and triggers are not imported or how to import them via SMS? How about the best way to copy a DB at Jodo? Thanks!
 
dman, typically the backup and restore using the online tool works but it seems the issue is with versions.

one option may be using export to SQL code, then running in query analyzer?
 
Stephen, thanks for the reply. Yes I tried the http://mssql9.xxx.com/mssql/Restore.aspx and get the following error:

MSSQL 2005 backup file was used.
Plese use a correct backup file.

Technically I'm not trying to restore, I need to copy. I did try using the following SQL query to restore the backup copy in the Jodo dir to the new DB but I get an error shown below as well.

RESTORE DATABASE [destination DB] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db_source.bak' WITH FILE = 1, KEEP_REPLICATION, NOUNLOAD, REPLACE, STATS = 10
GO

Error:
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Being less experienced with MS SQL I'm not sure how to stop the DB. I tried taking it offline yesterday which caused errors, made it so Jodo support could not access it either, and I was never able to access that DB again. I then deleted it and created a new DB with a new name because it would not allow me to use the old name.

I submitted a ticket for help Wednesday morning, FGY-94273-778, but most of my questions go unanswered. They are now telling me they have to take the web site down to restore the DB because the DB is currently in use. Does this need to be done? I would rather not take the whole web site offline. Can the DB just be taken offline? Can the DB not be copied when it is in use? Do I want a copy or a restore? maybe I'm confused but I am technically not restoring a DB but wanting to duplicate it.

This is getting a bit frustrating because I have limited access to the SQL server in SMS and I can't find or get answers to my questions. I just need to copy a DB and it seems overly complicated and error prone. Can anyone provide step by step guide to copy a DB at Jodo using MSSQL9 (SQL 2000) and SMS 2005? As mentioned I can import data but all primary keys and triggers are lost. Any help would be greatly appreciated.
 
Ok, so I think on e of the issue I was having with restoring the current DB to the new DB was that I was using a .BAK file created locally using SMS 2005 after adding some columns. So I downloaded a backup from Jodo and tried to restore to the new DB using the Jodo SQL web utility. Now instead of the "Please use a correct backup file" error I am getting a new error:

SQL server error occured.
Please contact administrator.

Another question, is it not possible to use the Jodo web utility to restore a DB to a newly created DB at Jodo with a different name? For example db_test to db_test1.
 
Another update... I now tried deleting and recreating the DB via Hsphere, then restoring to the new DB name. Same issue "SQL Error Occurred".

I am also trying "Generate Scripts" on the original DB via SMS and I get a script file that looks like it will create the DB for me, including the primary keys, etc, but I do not know how to then execute the query on a different DB. Any guidance on scripting this to a new DB? Thanks!
 
Try creating the database shell in control panel or get the details from the cp if its already there

write down the db name, user name , password and mssql server address
Launch management studio
if the connection window doesnt automatically pop up
click new query or connect -> database engine
server name is what you wrote down from the control panel eg mssql9.yourdomain or similar
Choose sql server authentication
enter your user name and password
if you click the options tab you can also enter the database name, although your login should still land you in the right database
Once your connected
Just run all your create table scripts in the query window
dont run the create db script as you already did it in the cp if everything goes well all your tables should be there ready for the data to be entered.

You can also enable identity inserts for tables when importing data if thats neccessary
Asking jodo to let you see your db from the server explorer in sms will enable you to add primary keys etc from the designer- providing there aint to many tables its another option
 
Hey Cdog,

Thanks for these instructions. I finally was able to duplicate the DB using the Tasks > Generate scripts, I think. I say "I think" because I had a support ticket in and tried the Jodo restore tool at the same time so it could have been one of the other items finally worked. But, I didn't get any errors after running the script so I am assuming it was your advice. Much obliged!!!
 
You must log in or register to reply here.
Back
Top