February 28, 2010 at 3:40 am
Hi all,
I am trying to transfer my tables and stored procedures from my local SQL2005 Express database to a shared server SQL2005 database.
I first tried "Copy Database" but that will not look for SQL Express files.
I then Imported the data and that worked however all of my Identities and table references were lost.
I then exported a script of the entire database (tables and sps) and ran it on the shared server. That worked as well. I then imported the data with no errors. However the indentity values are not the same as they are in the SQLExpress database and I need them to be.
Is there a way to import my tables, data, and sps where the identity values, identity settings, and table references are left like the originals?
Thanks for your help!
Eric
February 28, 2010 at 8:31 am
Have you attempted to use:
For your sp's use the following:
SELECT text FROM sys.syscomments
Note that the above will return the first 4000 characters of your procedure. With SSMS direct the output to a text file and then use that to recreate the procedures.
For the identity values look at SET IDENTITY_INSERT - but note that it can be set for ONLY one (1) table in a database. (Check Books On Line before using). Of course this will limit you to transferring data to/from one table at a time.
February 28, 2010 at 11:08 am
Is there a reason you cannot perform a backup/restore? If that cannot be done, download the trial edition of SQL Compare and SQL Data Compare from Redgate. Use the first tool to build the schema, the second one will synchronize the data.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 1, 2010 at 4:24 am
Hi Ron,
Thank you for your comments I will look into your suggestions later today or tomorrow.
Hi Jeffrey,
After reading your comment I thought “That would be too easy” lol. I attempted to do the backup and restore process. Of course the backup was no problem. I attached to the shared SQL2005 server and started the restore process. I was able to select the destination database (which has a slightly different name) but when I went to the Source for restore I selected from device. I received an error that I did not have the rights (which I don’t on the shared server) to the d:\MSSQL…. Folder. It would not let me select the folder where I have the backup file. Any suggestions?
Thanks again to both!
Eric
March 1, 2010 at 7:05 am
You need to talk to the administrator of that system and find out where you can put your backup file so it will be accessible to that system. You could also create a share somewhere on the network and access the share - but you need to grant permissions to the account running SQL Server and use UNC to reference the location.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply