0

I'm trying to migrate from microsoft sql server 2003 to sql server 2016. Importing the data went well, using Microsoft SQL Management Studion 2016, but the views were not imported. And here is my request, how can I import the views?

At this point I tried:

  • Generate a creation script from the old database, but it seems back in 2003, this option didn't existed
  • Make a query in the old SQL server to list all the views, copy them and recreate them manually in the new server, but no queries worked to retrieve all the views
  • Copy the .MDF and.LDF file from the old server, and attach them to the new server, but I have the following error:

=

==================================

Fail to join database for serber « DESKTOP-53C6I45\SQLEXPRESS ».  (Microsoft.SqlServer.Smo)

------------------------------
To get help, click on : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.16106.4+((SSMS_Rel_16_5).170125-2137)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attacher+la+base+de+données+Server&LinkId=20476

------------------------------
Software location :

   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach()
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()

===================================

An exception was produced during the execution of batch Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Location of the software :

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)

===================================

The database 'Labeint' can't be upgraded, because its non final version (539) isn't managed by this version of SQL Server. You can't open a database which is incompatible with this version of sqlservr.exe. You must rereate the database.
Impossible to open the new database 'Labeint'. Abandon of CREATE DATABASE. (.Net SqlClient Data Provider)

------------------------------
To get help, click on : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=950&LinkId=20476

------------------------------
Server name : DESKTOP-53C6I45\SQLEXPRESS
Error number : 950
Gravity : 20
State: 1
Line number : 1


------------------------------
Location of the software :

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

The thing is the views are complex in an old project, getting data I don't know where for a project which is a black box to me, otherwise I would have recreated them manually, trying to figuring out how they were made.

Gaetan L'Hoest
  • 60
  • 9

1 Answers1

0

You can't wholsesale migrate the database files this way. You're trying to upgrade a Sql Server 2000 DB (v539) to Sql Server 2016 and there is not upgrade path for that. At a push you'd probably need to go from 2000 to 2008R2, then 2008R2 to 2016 if this is your chosen (only) path.

An alternative is to download an older version of Red-Gate Sql Compare (v11 or before) and run that in trial mode against an empty 2016 DB.

https://www.red-gate.com/products/old-versions

Rachel Ambler
  • 1,440
  • 12
  • 23
  • So there is no direct ways to extract the views from a Sql Server 2000 DB? I'll try Red-Gate and keep you updated. – Gaetan L'Hoest Jun 08 '17 at 11:48
  • I've not used Sql Server 2000 in simply _forever_ ago so I've lost track as to it's many and onerous restrictions (compared to today). – Rachel Ambler Jun 08 '17 at 11:57
  • I tried you solution, and it worked. I retrieved the .ldf and the .mdf from Microsoft Server 2003, attached it in Microsoft SQL Server 2005 on a virtual machine running Windows XP SP2, backed up the databases, and was able to restore the .bak on Microsoft SQL Server 2016. Thank you! – Gaetan L'Hoest Jun 25 '17 at 13:10