Out of Memory Error on SQL
I have scripted out a SQL2008R2 DB in order to recreate the DB as a 2005 version. I ran the script with no issues, but when I run the script for the 2005 DB I receive an "Out of Memory" error. How much memory do I need to complete this as it is an urgent matter to get htis back to 2005 for our client.
The DB is 210MB. I deleted the Log file so that is under a meg. The script is 240MB itself.
I just need to know how I can get this script run to create my sql2005 DB.
Please help.
The DB is 210MB. I deleted the Log file so that is under a meg. The script is 240MB itself.
I just need to know how I can get this script run to create my sql2005 DB.
Please help.
knightEknight
Assuming the issue is with the size of the script, why don't you break the script into three or four separate scripts, and run them consecutively.
You may consider doing it with SQL Management Studio 2008, if you have access to a 2005 Server from the 2008 SQL server.
I found this to try:
-> Open Your Management Studio 2008
-> Connect to a MS SQL Server 2005 (The 2005 SQL Server which You wish to copy data to, or another one, and you just copy the result backup-file)
-> Greate a Temp Database (name it whatever) -> Right click your temp database
->Task ==> Import Data
-> Choose your 2008 Database Engine (with Specific Databases--The database with all the data )
-> Use the Import Wizard to Import them
->(This might take a while)
-> After everything done, you have successfully transfered all your 2008 data(tables or row data) into 2005 SQL server
->Then make a database restore file now-
>You can use this file to restore data for both 2008/2005
I found this to try:
-> Open Your Management Studio 2008
-> Connect to a MS SQL Server 2005 (The 2005 SQL Server which You wish to copy data to, or another one, and you just copy the result backup-file)
-> Greate a Temp Database (name it whatever) -> Right click your temp database
->Task ==> Import Data
-> Choose your 2008 Database Engine (with Specific Databases--The database with all the data )
-> Use the Import Wizard to Import them
->(This might take a while)
-> After everything done, you have successfully transfered all your 2008 data(tables or row data) into 2005 SQL server
->Then make a database restore file now-
>You can use this file to restore data for both 2008/2005
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay here is where I am.
Ashilo:
The Source 2008r2 DB is already set to Compatibility Level of 90 (2005). I tried to just backup the DB from 2008R2 and restore it to 2005 and get an error as seen in the image.
Sunezapa:
I then tried your way and received the following warnings. I am only showing a portion of the copying rows messages as there are too many to fit in one screen shot.
Not sure if the import warnings matter at all, but I am going to test the DB now.
Compatibility-Level.jpg
Data-Import.jpg
Ashilo:
The Source 2008r2 DB is already set to Compatibility Level of 90 (2005). I tried to just backup the DB from 2008R2 and restore it to 2005 and get an error as seen in the image.
Sunezapa:
I then tried your way and received the following warnings. I am only showing a portion of the copying rows messages as there are too many to fit in one screen shot.
Not sure if the import warnings matter at all, but I am going to test the DB now.
Compatibility-Level.jpg
Data-Import.jpg
ASKER
On the import it did not get everything needed either. It is missing all the stored procs and who knows whatelse.
hi
it looks like your using some kind of a multiplexd or partitioned backup file
and not using all the files in the restore.
it looks like your using some kind of a multiplexd or partitioned backup file
and not using all the files in the restore.
ASKER
I am just creating a Full backup and then restoring. All from a single bak file. Do I need to do this a different way.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How would I divide the script and know where to split it up at as it is scripting Schema and Data? I guess I can create the DB with the Schema script which works fine. Then split up the data only. I will try that.
I guess it is the data that fill the Script. So you need to split in between some data.
The script comes with creating schema - fill data, create next schema - fills data, if I remember correct.
So split somewhere where creating a new schema is starting...
But of course the script will depend on the tool you used for creating the script.
If the many data is all in one table, then you can not benefit from splitting between inserting data, and have to split somewhere (at end of a line) and edit the ending of the first script and the beginning of the next part (by inserting a copy of the "INSERT INTO... )...
You can use tools as ultraedit to edit those large files.
The script comes with creating schema - fill data, create next schema - fills data, if I remember correct.
So split somewhere where creating a new schema is starting...
But of course the script will depend on the tool you used for creating the script.
If the many data is all in one table, then you can not benefit from splitting between inserting data, and have to split somewhere (at end of a line) and edit the ending of the first script and the beginning of the next part (by inserting a copy of the "INSERT INTO... )...
You can use tools as ultraedit to edit those large files.
ASKER
Okay I created the table with the script for schema only and worked perfectly. I then split the data only script into 3 scrips. The first ran perfectly, but the second one ran and received the following error. How do I fix?
Cannot insert explicit value for identity column in table 'netformdetail' when IDENTITY_INSERT is set to OFF.
Cannot insert explicit value for identity column in table 'netformdetail' when IDENTITY_INSERT is set to OFF.
ASKER
Just found out it was missing the
SET IDENTITY_INSERT [dbo].[netformdetail] ON
In the beginning. I am still working on getting the data in but splitting the script up seems to be the best route so far. I will update the question after I am able to test or if I run into more issues.
SET IDENTITY_INSERT [dbo].[netformdetail] ON
In the beginning. I am still working on getting the data in but splitting the script up seems to be the best route so far. I will update the question after I am able to test or if I run into more issues.
add before the insert:
you will have to do similar for other tables that have Identity column, if you want to keep the values already created in the Identity fields.
You may do it by Replace some text in the script, and add the sentences - BUT you have to change the TABLENAME to the actual tablenames for inserting...
Don't panic - just enjoy- when all the job is suddenly done :-)
SET IDENTITY_INSERT netformdetail ON
GOyou will have to do similar for other tables that have Identity column, if you want to keep the values already created in the Identity fields.
You may do it by Replace some text in the script, and add the sentences - BUT you have to change the TABLENAME to the actual tablenames for inserting...
Don't panic - just enjoy- when all the job is suddenly done :-)
ASKER
Appreciate the help from both of you. Splitting the script into multiple scripts worked perfectly. I actually had to split it into 3 scripts to get it to work. Once again thanks for all you help.