July 7, 2016 at 5:49 am
Hi,
I have to migrate all my data from 2012 to 2008 server, Since restore doesn't work, I am generating scripts so that i can run on my 2008 server.
My issue is when i generate scripts for DB script generation fails with following error. There are around 400 tables and the scripting fails somewhere in between while generating the script for the 200th table.
I would appreciate an help on this.
Thanks, in advance.
Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Byte[] buff, Int32 offset, Int32 len, Int32& totalRead) at System.Data.SqlClient.TdsParserStateObject.TryReadChar(Char& value) at System.Data.SqlClient.TdsParser.TryReadPlpUnicodeCharsChunk(Char[] buff, Int32 offst, Int32 len, TdsParserStateObject stateObj, Int32& charsRead) at System.Data.SqlClient.TdsParser.TryReadPlpUnicodeChars(Char[]& buff, Int32 offst, Int32 len, TdsParserStateObject stateObj, Int32& totalCharsRead) at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.TryReadColumnData() at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly) at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn) at System.Data.SqlClient.SqlDataReader.GetSqlValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetSqlValue(Int32 i) at System.Data.SqlClient.SqlDataReader.GetProviderSpecificValue(Int32 i) at Microsoft.SqlServer.Management.Smo.DataEnumerator.FormatValueByType(String columnName, Int32 columnIndex) at Microsoft.SqlServer.Management.Smo.DataEnumerator.GetNextInsertStatement() at Microsoft.SqlServer.Management.Smo.DataEnumerator.MoveNext() at Microsoft.SqlServer.Management.Smo.SingleFileWriter.ScriptData(IEnumerable`1 dataScript, Urn table) at Microsoft.SqlServer.Management.Smo.FilePerObjectWriter.ScriptData(IEnumerable`1 dataScript, Urn table) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptDataToWriter(IEnumerable`1 dataScripts, Urn urn) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateSpecialUrn(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
July 7, 2016 at 5:55 am
Since you're getting a timeout table, I would say the easiest thing would be to script fewer tables at a time.
John
July 7, 2016 at 6:17 am
DO NOT script the data!
First because you'll get exactly these errors on larger tables. Second because the resultant script will probably crash Management Studio when you go to run it due to the size, unless this DB is a couple hundred MB.
Script the DB and the object definitions, then use bcp out (command line tool) or import/export to export each table to a csv or similar file. Then once you've created the DB on the destination server you can use bcp in, BULK INSERT or import/export to reload the tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2016 at 8:20 am
Thanks a lot for the reply john
July 7, 2016 at 8:24 am
Second-the-motion on Gale's suggestion. Script objects, import data. Don't script data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2016 at 8:24 am
You're welcome. I didn't realise you were scripting out the INSERTs as well, and I agree with Gail that you should script out just the object creation, and use bcp, SSIS or your favourite other ETL technology to move the data.
John
July 7, 2016 at 8:25 am
Thanks for the Reply gail, but however I was able to script out all data by changing execution time out settings, since I have all data now can I use them on my destination server? And
Can I transfer stored procedures and views using import /export
July 7, 2016 at 8:28 am
Well, yes - if you've managed to move the data, even if you didn't do it in the most efficient way, you can still use it. But you can only use Import/Export for data - you'll need to script out stored procedures, views and other objects.
John
July 7, 2016 at 10:42 pm
Kudos, Thanks for the solution john.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply