cannot move large SQL DB from old reseller account to new VPS account

[n8nt]

I was wondering if anyone else ran into this. I still have my SoftSysHosting reseller account and will have it until I get all of my current websites moved to my new VPS account. Everything has been going relatively smooth but now I've run into a problem. One of my webistes has a very large sql database. I created a sql script to move my database from the reseller account to the vps account. However, that databas is large as it contains almost 25 years worth of pdf docs. The sql script is just under 400 mb in size. When I try to load the script I get an error saying "The operation could not be completed. Not enough storage is available to complete this operation."

So next I tried using the backup file from my reseller account but I could not get that into the vps account. I tried doing a restore using that .Bak file that was created by the database maintenance program on the reseller account. The restore file seems to be locke to the reseller account - not surprising I guess. So, what else to do? I'm trying now to do the following:
create a new db login on the vps with the same credentials as the login for the database I'm trying to move.
create a new db on the VPS with the same name as the one I'm trying to move.
from the reseller, publish to provider with schema only - script will be loadschemaonly.sql
now, on the vps open the loadschemaonly.sql and then execute that script which should build the database and all the tables only without any data.
Once that's in I'll thne export the data from the reseller account to the new vps database.

I tried just doint the export and import back to the new vps empty data base and it did import thte data but not the stored procedures.

If anyone has done this before and this is not the right procedure, please let me know.

Thanks

[Rick - SoftsysHosting]

Hi,

Well, SQL db restore in DNP is not locked to reseller account. There must be something going amiss with restoration on your VPS which would be preventing this. We have quite a good amount of customers using this regularly to restore database from DNP bak file on their local/development machines and move back updated version to live server without any issues. Using db publishing wizard should help; however, if it'd be loading up entire SQL in db engine instance of VPS, you'll again end up with same out of storage message. I'd rather recommend you to split your query into multiple queries and execute them in chunks so that memory usage remains minimal.

Thanks!

[n8nt]

Hmmm. I wonder if my problem might be that I installed SQL Express on my VPS. The problem with splitting up my sql is that the one table is over 100 mb and just scripting it results in almost 400 mb.

When I tried to restore using the file I backed up from the reseller I got an error that says:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'S1\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'ATCODB' database. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
This leads me to believe that maybe I need to install SQL SERVER 2008 Standard. I'm not sure if I can install that along sid SQL EXPRESS - have been researching that but not found anything to say yes it can or no it can't...

So I'm going to goto my reseller account, back up the db again, delete the big table, then use publish to provider to get all but the one table, then I'll restore the db back on the reseller and go to my vps and build my atcodb which should have all but the big table which I'll then use BCP to insert into the db.

If that doesn't work, then maybe I'll have to try installing SQL Server 2008 Standard and hope it won't interfere with the SQL EXPRESS that's already there.