• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

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.
0
mrwarejr
Asked:
mrwarejr
  • 7
  • 4
  • 2
  • +1
2 Solutions
 
knightEknightCommented:
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.
0
 
sunezapaCommented:
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
0
 
Aaron ShiloCommented:
hi

the problem is the script size it self.
i would suggest on of the two.

A:
1. backup the database on 2k8r2
2. restore it on sk8r2 ith a different name
3. change the compatibility level to 2005
4. backup the db again this time with comp level 2005
5. restore it to the 2005 server
6. rename the db to the original name.

B:
cut the script into smaller parts.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
mrwarejrAuthor Commented:
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
0
 
mrwarejrAuthor Commented:
On the import it did not get everything needed either.  It is missing all the stored procs and who knows whatelse.
0
 
Aaron ShiloCommented:
hi

it looks like your using some kind of a multiplexd or partitioned backup file
and not using all the files in the restore.

0
 
mrwarejrAuthor Commented:
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.
0
 
sunezapaCommented:
all the Informations are just fine and to forget...
The Warnings MAY be something, but general I do not look, if it is working - it could issues as "I can't find exactly match in types, so I just use the nearest type available"...
i any ERRORS I would study, but then the import stops and you will know...
If the Big Script is because of a lot of data, you may use the import, and then script the Stored Procedures... and the wahtever's.

Or: try the proposed way of dividing the full Scripts into smaller ones.
0
 
mrwarejrAuthor Commented:
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.  
0
 
sunezapaCommented:
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.
0
 
mrwarejrAuthor Commented:
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.
0
 
mrwarejrAuthor Commented:
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.
0
 
sunezapaCommented:
add before the insert:
SET IDENTITY_INSERT netformdetail ON
GO

Open in new window


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 :-)
0
 
mrwarejrAuthor Commented:
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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now