h1

Backup-Restore SQL Server yang “Backward Compatible”

31 Agustus 2010

Suatu kali saya diminta untuk merestore database dari database server SQL Server 2008 ke database server SQL Server 2005.
File yang di restore berekstensi .bak. Tapi setiap kali akan merestore, selalu muncul pesan kesalahan

Server: Msg 3241, Level 16, State 7, Line 1
The media family on device 'C:\nama_database.bak' is incorrectly formed. SQL Server cannot process this media family.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Selidik punya selidik ternyata SQL Server memang tidak backward compatible (IMHO). Saya mendapatkan pencerahan setelah membaca forum : http://www.sqlservercentral.com/Forums/Topic536132-357-2.aspx

Disini juga diberikan beberapa pesan kesalahan yang pada umum muncul ketika merestore database dari SQL server dengan versi yang lebih baru ke versi sebelumnya.

SQL Server 2008 R2 to SQL Server 2008
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

SQL Server 2008 R2 to SQL Server 2005

Server: Msg 3241, Level 16, State 7, Line 1
The media family on device ‘f:\temp\test001_sql2008r2.bak’ is incorrectly formed. SQL Server cannot process this media family.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

SQL Server 2008 R2 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 661. The server supports version 539 and cannot restore or upgrade this database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

SQL Server 2008 to SQL Server 2005

Server: Msg 3241, Level 16, State 7, Line 1
The media family on device ‘f:\temp\test001_sql2008.bak’ is incorrectly formed. SQL Server cannot process this media family.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

SQL Server 2008 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 655. The server supports version 539 and cannot restore or upgrade this database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

SQL Server 2005 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Solusi yang saya temukan adalah melakukan backup menggunakan script wizard dari SQL Server Management Studio. Dari database yang ingin dibackup, klik kanan pilih Tasks – Generate Scripts … Maka akan muncul jendela baru yang menuntun kita melakukan backup dengan menggenerate script (.sql). Disini kita juga dapa memilih Object yang dibackup (Schema, Store Procedure, Tables, User-defined functions dan Users). Hasilnya dapat berupa file .sql, copyan script di clipboard atau membuka jendela baru di SQL Server Management Studio yang berisi script hasil generate wizard tadi.

Kelemahan dari cara ini adalah, data yang sudah ada tidak ikut terbawa ke script backup. Kita dapat menggunakan tools tambahan untuk membantu memindahkan data. Penulis sudah mencoba Red Gate SQL Data Compare.  Kalau ada rekan-rekan yang memiliki alternatif tool lain, silakan share pengalamannya. Semoga membantu.

Iklan

One comment

  1. nice post… =D
    coba selidikin DTS, gunanya mirip kaya gitu, tapi dia bisa beda platform, SQL server, oracle, dll…

    Itu seh di Sql 2000, kl di sql 2008 uda beda namanya kl ga salah…



Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: