SQL: Export a SQL Server 2008 DB to SQL Server 2005

Since the 2008 version uses a different format for .bak files, a backup done in 2008 cannot be restored from 2005. A possible workaround is is creating, in 2008, a Script targeted at 2005, and run that scrit in 2005. The steps:

  1. Open Managment Studio 2008 and connect to the instance in which the DB we wanna export is.
  2. Right click the database, and hit Tasks->Generate Scripts.
  3. Hit Next in the initial Dialog.
  4. Check "Script all objects in the selected database" before hitting Next. This guarantees that not only the schema is exported, but the contents too.
  5. In the next dialog, we have lots of options. The ones we have to set before hitting Next are the following:
    • Script for Server Version : SQL Server 2005.
    • Script Data : True.
    • If the DB to export wasn't created in the 2005 server, which is usually the case, set 'Script Database Create' to 'True'.
  6. Select 'Script to File' so that we create the .sql script. We can set the path where we want Management Studio to drop it in a textbox. Also, check the 'Single file' option so that the script is only one file. Hit 'Next'.
  7. In this dialog we can see a summary of what the script does. Check it's coherent before clicking Finish.
  8. Once Management Studio is done, open it and check for lines like these: 
    CREATE DATABASE [Northwind] ON PRIMARY

(NAME = N'Northwind', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.mdf' ,
SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

(NAME = N'Northwind_log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO
    Correct the paths so that they match the ones that the target server (2005) uses.
  9. It might be necessary to comment out lines like these; in my case, it wasn't necessary: 
    --EXEC sys.sp_db_vardecimal_storage_format N'Northwind', N'ON'
--GO
  10. Finally, copy the .sql file to the machine where the 2005 server lives, open it from Management Studio 2005 (File->Open) and hit Execute. In my particular case, I got some errors when the script tried to create some users that already existed, but that wasn't fatal. The script continued and brought me all the tables and their rows.
Reference: SQL Server Central

Comments

Post a Comment

Popular posts from this blog

VB.NET: Raise base class events from a derived class

Spanish version / Versión en Español Say we: Have a class we want to divide into subclasses The future base class has events The refactoring leads us to the need of raising an event declared in the base class, from a derived class. When we try to do point 3, we will get a compile error saying that a derived class cannot raise events from a base class. To get around this, we add a protected method in the base class which encapsulates the RaiseEvent call, and call that method from the derived class using MyBase. This way: In the base class (parameters as needed): Protected Overridable Sub OnMessageGenerated(ByVal NewSender As Object, ByVal NewMessage As String) RaiseEvent MessageGenerated(NewSender, New MessageEventArgs(NewMessage)) End Sub And in the derived class: 'Do stuff 'Raise base class event MyBase.OnMessageGenerated( Me, "Message" ) 'Do more stuff... References: Mark Gilbert post
Keep reading

Apache Kafka - I - High level architecture and concepts

Image
Spanish version / Versión en Español First of all, what is Apache Kafka ? An initial definition for it is "a high-throughput distributed messaging system". Breaking this down a bit: High throughput: One of the main goals of Kafka is to process as much messages per unit of time as possible, in a scalable and fault-tolerant way. For example, LinkedIn , which is where Kafka originated, needs to achieve throughputs in the order of 20 million messages per second and 3 GB of data per second. Distributed messaging system: In a generic distributed system , networked computers work together for a common goal. In Kafka's case, that goal is to send data from points A 1 , A 2 , ... A N , to B 1 , B 2 , ... B M . A common benefit of this kind of system, which is crucial to Kafka's success, is horizontal scalability : the ability to handle higher and higher volumes of data by adding nodes to the system (not to be confused with  vertical scalability , whic
Keep reading

Upgrading Lodash from 3.x to 4.x

Spanish version / Versión en Español There are many breaking changes from version 3.x to 4.x; all changes are detailed in the Lodash changelog . There is also a list of deprecated functions , with a regex you can use to detect usages in your code. However, since it is not an exhaustive list, I will list the changes I encountered when performing my own migration, to help anyone undergoing the same process. Direct renames This section includes functions in which only the name changed, i.e. the arguments and return value are the same. 3.x 4.x contains includes first head rest tail invoke invokeMap padLeft padStart padRight padEnd Direct renames if not using thisArg This section includes functions in which the only change is dropping "thisArg". If you are not using it, you can just rename. If you are using thisArg in 3.x, it can be dropped if you are using iteratee to acce
Keep reading