I have generated the sql script of an Umbraco database on a sharing host. Usually I can just execute the sql script to a local database, but this Umbraco installation contains a lot of media items and the sql script has grown pretty large.
E.g. a simple website I have is the size of the sql script 4.32 MB .. this sql script has a size of 121 MB ..
I think there is a maximum size to execute the sql script in SSMS (SQL Server Management Studio) .. and that you can execute the script from Windows Command prompt..
is it a SQL Server database? If so it might be better to use backup/restore. I have had problems with MySQL in the past where the script is too large. Either splitting the file into 2 or removing the insert statements from the umbracolog table was enough to get it to work
Often when it's hosted at at host like UnoEuro in this case.. or Surftown.. you don't have permission to create use the right click backup to create the .bak file and need to script the database..
Have you tried contacting unoeuro to find out if they can help you out doing it using a .bak file perhaps? They might not have any backup etc. but perhaps it's woth the shot.
I have wrote a support question from the Unoeuro account, but haven't got an answer yet.. it's probably cheap, but often I also find the support slow..
I can probably get the .bak file from Unoeuro as they create a backup each day: https://www.unoeuro.com/support/faq/14/93/ But then backup is probably created late night, night or early morning.. and I would also like to create a backup, when I need it.. e.g. before upgrading Umbraco.
It works okay to generate the script and execute the script to restore.. but it seems to give a problem with memory when executing large sql scripts in SSMS..
Hi Bjarne, I have been in this situation a couple of times, the first thing i try and do is clean up the database, by that i mean deleting all log files, and also deleting old versions of content, i normally do this using http://our.umbraco.org/projects/backoffice-extensions/falm-housekeeping , once that is done i then script the database and then it all works correctly.
If cleaning up the database is not an option then i tend to generate a SQL script for the databse (no data) ... find where all the table creation finishes and only run the script to generate the tables (no relations), once that is done i can use a database import to get all the data from the live database, once i have all the data in the tables i then run the rest of the script to generate all the relationships in the databse. A bit long winded but it works
Hi Bjarne, from my experience you can simlpy delete all rows from the umbracoLog table and that should clear out all the logs. Cleaning out old versions of content is tricker and the mentioned package will allow you to easily do this, but you will need to change the action to cascade on delete (the notes in the package explain how and where to do this).
Good to see that you managed to get it working anyway, as for the package it is ont of the standard ones i install along witgh ImageGen and uComponents
Generally I am careful with installing a new package as it not always might be compatible with the newest versions of Umbraco and might break something.. so I would ensure I have a complete backup first..
Restore database from large sql script
Hi..
I have generated the sql script of an Umbraco database on a sharing host. Usually I can just execute the sql script to a local database, but this Umbraco installation contains a lot of media items and the sql script has grown pretty large.
E.g. a simple website I have is the size of the sql script 4.32 MB .. this sql script has a size of 121 MB ..
I think there is a maximum size to execute the sql script in SSMS (SQL Server Management Studio) .. and that you can execute the script from Windows Command prompt..
http://www.diaryofaninja.com/blog/2009/09/25/running-large-mssql-scripts-amp--resulting-memory-errors
But when I write something like sqlcmd -S MYSERVER\SQLEXPRESS -i "C:\myscript.sql” in command prompt it return a syntax error in line 79871.
Do someone know why this fails or have expirence with scripting and restoring large Umbraco database?
There doesn't seem to be any problems with generating the script and only take few seconds (30 seconds or less).
/Bjarne
is it a SQL Server database? If so it might be better to use backup/restore. I have had problems with MySQL in the past where the script is too large. Either splitting the file into 2 or removing the insert statements from the umbracolog table was enough to get it to work
Yes, it's an MSSQL database.
Often when it's hosted at at host like UnoEuro in this case.. or Surftown.. you don't have permission to create use the right click backup to create the .bak file and need to script the database..
Have you tried contacting unoeuro to find out if they can help you out doing it using a .bak file perhaps? They might not have any backup etc. but perhaps it's woth the shot.
/Jan
Hi Jan
I have wrote a support question from the Unoeuro account, but haven't got an answer yet.. it's probably cheap, but often I also find the support slow..
I can probably get the .bak file from Unoeuro as they create a backup each day: https://www.unoeuro.com/support/faq/14/93/
But then backup is probably created late night, night or early morning.. and I would also like to create a backup, when I need it.. e.g. before upgrading Umbraco.
It works okay to generate the script and execute the script to restore.. but it seems to give a problem with memory when executing large sql scripts in SSMS..
/Bjarne
Hi Bjarne, I have been in this situation a couple of times, the first thing i try and do is clean up the database, by that i mean deleting all log files, and also deleting old versions of content, i normally do this using http://our.umbraco.org/projects/backoffice-extensions/falm-housekeeping , once that is done i then script the database and then it all works correctly.
If cleaning up the database is not an option then i tend to generate a SQL script for the databse (no data) ... find where all the table creation finishes and only run the script to generate the tables (no relations), once that is done i can use a database import to get all the data from the live database, once i have all the data in the tables i then run the rest of the script to generate all the relationships in the databse. A bit long winded but it works
Hope it helps
Hi Asif
I haven't tried the package you mention, but perhaps I should take a look at it sometime..
Yes, I also thought of generating multiple sql script for that as you say and like this tool splitting the script: https://bitbucket.org/mrshrinkray/projects/src/9c27c44b1540eeb5d49e274af54d7aba1f73ce14/Umbraco.DataExport
However I passed by this page http://www.yetanotherchris.me/home/2010/5/5/umbraco-hacks-for-developers.html and saw taht I could use this line to clean up the umbracoLog table: TRUNCATE TABLE [dbo].umbracoLog
After a run this sql script has a size of 110 MB instead of 121 MB ... and I could run the script in SSMS.. for now.
/Bjarne
Hi Bjarne, from my experience you can simlpy delete all rows from the umbracoLog table and that should clear out all the logs. Cleaning out old versions of content is tricker and the mentioned package will allow you to easily do this, but you will need to change the action to cascade on delete (the notes in the package explain how and where to do this).
Good to see that you managed to get it working anyway, as for the package it is ont of the standard ones i install along witgh ImageGen and uComponents
Also forgot to mention i normally empty the recycle bin (content and media) as well, this way the system should be as minimal as possible
Hi Asif
Generally I am careful with installing a new package as it not always might be compatible with the newest versions of Umbraco and might break something.. so I would ensure I have a complete backup first..
It seems that DELETE and TRUNCATE both remove the rows, but there are some differences: http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in-truncate-and-delete-in-sql-server/ - amount others that truncate will reset identity columns.
But now I have the sql script and could create a backup (.bak) from the local database copy.
I have seen the package before, but never tried it.. that might be useful..
/Bjarne
is working on a reply...