انتقال دیتابیس SQL Server 2008 به ۲۰۰۵: روش درست و خطاهای رایج

یک فایل بک‌آپ .bak از SQL Server 2008 دارید و می‌خواهید آن را روی سروری که SQL Server 2005 دارد Restore کنید، اما SQL Server خطا می‌دهد. فایل .mdf را هم برمی‌دارید و روی نسخهٔ ۲۰۰۵ Attach می‌کنید و باز خطا می‌گیرید. این باگ نیست و با Service Pack یا تنظیمات هم حل نمی‌شود: SQL Server هیچ مسیر رسمی برای برگشت به نسخهٔ پایین‌تر ندارد. برای انتقال دیتابیس SQL Server2008 به ۲۰۰۵ باید «ساختار» و «داده» را جداگانه منتقل کنید، نه خود فایل دیتابیس را.

راه اول: Restore بک‌آپ یا Attach کردن فایل MDF — چرا جواب نمی‌دهد

در بسیاری از یادداشت‌های قدیمی (از جمله نسخهٔ پیشین همین مطلب) گفته شده که کافی است بک‌آپ ۲۰۰۸ را روی ۲۰۰۵ Restore کنید یا فایل MDF را Attach کنید. این توصیه نادرست است و باید اصلاح شود.

هر دیتابیس SQL Server یک «شمارهٔ نسخهٔ داخلی فایل» دارد که هنگام ساخت یا ارتقا روی هدر فایل نوشته می‌شود. این شماره برای SQL Server 2005 برابر ۶۱۱ است و از Service Pack 2 به بعد به ۶۱۲ تغییر می‌کند؛ برای SQL Server 2008 برابر ۶۵۵ و برای ۲۰۰۸ R2 برابر ۶۶۱ است. موتور دیتابیس فقط فایل‌هایی را باز می‌کند که شمارهٔ نسخهٔ آن‌ها مساوی یا کمتر از نسخهٔ خودش باشد. فایل بک‌آپ هم همین اطلاعات را در خود دارد.

نتیجه در عمل، هنگام Restore این خطاست:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 10.00.xxxx.
That version is incompatible with this server, which is running version 9.00.xxxx.
Either restore the database on a server that supports the backup,
or use a backup that is compatible with this server.

و هنگام Attach کردن فایل MDF این خطا (عدد ۶۱۲ روی یک نمونهٔ ۲۰۰۵ با SP2 یا بالاتر دیده می‌شود؛ روی نسخهٔ بدون Service Pack عدد ۶۱۱ است):

Msg 948, Level 20, State 1, Line 1
The database 'MyDB' cannot be opened because it is version 655.
This server supports version 612 and earlier.
A downgrade path is not supported.

عبارت A downgrade path is not supported دقیقاً همان چیزی است که باید جدی بگیرید. Detach/Attach، کپی کردن فایل‌ها، یا Copy Database Wizard در حالت Detach/Attach هیچ‌کدام این محدودیت را دور نمی‌زنند. تنها حالتی که Restore جواب می‌دهد این است که آن بک‌آپ اصلاً روی نسخهٔ ۲۰۰۵ یا پایین‌تر گرفته شده باشد.

راه دوم: تغییر Compatibility Level به ۹۰ — لازم است اما کافی نیست

در Management Studio 2008، روی دیتابیس راست‌کلیک کنید، Properties و سپس بخش Options؛ گزینهٔ Compatibility level را روی SQL Server 2005 (90) بگذارید. معادل T-SQL آن روی نسخهٔ ۲۰۰۸:

ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 90;

یک اصلاح کوچک نسبت به متن قدیمی: عدد درست ۹۰ است، نه ۹٫ عدد ۹٫۰ شمارهٔ نسخهٔ محصول SQL Server 2005 است، اما مقداری که در Compatibility Level وارد می‌شود ۸۰ برای ۲۰۰۰، ۹۰ برای ۲۰۰۵ و ۱۰۰ برای ۲۰۰۸ است. ضمناً دستور ALTER DATABASE ... SET COMPATIBILITY_LEVEL از SQL Server 2008 به بعد اضافه شده؛ روی خود نسخهٔ ۲۰۰۵ باید از رویهٔ قدیمی sp_dbcmptlevel استفاده کنید که مایکروسافت از ۲۰۰۸ به بعد آن را منسوخ (deprecated) اعلام کرده است:

EXEC sp_dbcmptlevel 'MyDB', 90;

مهم‌ترین نکته این است: Compatibility Level فقط رفتار زبان T-SQL و بهینه‌ساز را عوض می‌کند و هیچ تغییری در فرمت فیزیکی فایل دیتابیس نمی‌دهد. دیتابیسی که روی ۲۰۰۸ ساخته شده، حتی با Compatibility Level برابر ۹۰، همچنان فایل نسخهٔ ۶۵۵ دارد و روی ۲۰۰۵ باز نمی‌شود. پس این کار به‌تنهایی مهاجرت نیست؛ ارزش واقعی‌اش این است که قبل از انتقال، آن را روی ۲۰۰۸ فعال کنید و اپلیکیشن و Stored Procedure ها را تست کنید تا مطمئن شوید چیزی به قابلیت‌های اختصاصی ۲۰۰۸ وابسته نیست. توجه کنید که تغییر Compatibility Level روی یک دیتابیس در حال سرویس‌دهی می‌تواند Query Plan ها را عوض کند؛ این کار را در ساعت کم‌ترافیک و با امکان برگرداندن مقدار قبلی انجام دهید.

راه سوم: تهیهٔ Script با Server Version برابر ۲۰۰۵ — روش درست

ایدهٔ روش این است که به‌جای بردن فایل، ساختار دیتابیس را به شکل اسکریپت T-SQL سازگار با ۲۰۰۵ بیرون بکشید، روی سرور ۲۰۰۵ اجرا کنید و بعد داده‌ها را جداگانه منتقل کنید.

پیش‌نیازها

• دسترسی به هر دو سرور با کاربری که حداقل عضو نقش db_owner در مبدأ و dbcreator در مقصد باشد.
• یک بک‌آپ کامل و تست‌شده از دیتابیس ۲۰۰۸ قبل از شروع.
• فضای دیسک کافی روی سرور ۲۰۰۵ برای دیتابیس و رشد فایل Log هنگام درج انبوه داده.
• توجه به محدودیت نسخهٔ Express: حداکثر حجم هر دیتابیس در SQL Server 2005 Express برابر ۴ گیگابایت است. اگر دیتابیس مبدأ بزرگ‌تر است، انتقال در نیمهٔ راه شکست می‌خورد.
• هم‌خوانی Collation دو سرور را بررسی کنید؛ تفاوت Collation باعث خطا در مقایسه‌ها و Join ها می‌شود.

گام ۱: ساخت اسکریپت ساختار

در Management Studio 2008 روی دیتابیس راست‌کلیک کنید، Tasks و سپس Generate Scripts. اشیای موردنظر را انتخاب کنید و در صفحهٔ تنظیمات، دکمهٔ Advanced را بزنید. اینجا دو گزینه تعیین‌کننده است:

• Script for Server Version را روی SQL Server 2005 بگذارید. این همان چیزی است که SSMS را وادار می‌کند نحو (syntax) سازگار با ۲۰۰۵ تولید کند. در متن قدیمی از آن با نام «ورژن سرور ۲۰۰۵ یا ۹٫۰» یاد شده بود؛ منظور همین گزینه است.
• Types of data to script را انتخاب کنید. بسته به نسخهٔ SSMS، این گزینه ممکن است با نام Script Data و مقدار True/False دیده شود. برای دیتابیس‌های کوچک حالت Schema and data کار را یک‌مرحله‌ای می‌کند؛ برای دیتابیس‌های بزرگ فقط Schema only بگیرید و داده را در گام ۴ منتقل کنید.

فایل خروجی به‌صورت پیش‌فرض در پوشهٔ Documents (My Documents) ساخته می‌شود. مسیر و نام فایل در همان صفحه قابل تغییر است.

اگر شیئی از قابلیت‌های اختصاصی ۲۰۰۸ استفاده کند، اسکریپت روی ۲۰۰۵ اجرا نخواهد شد و باید دستی بازنویسی شود. رایج‌ترین موارد: نوع‌داده‌های date، time، datetime2، datetimeoffset، geography، geometry و hierarchyid؛ دستور MERGE؛ Table-Valued Parameter؛ Sparse Column؛ Data Compression؛ FILESTREAM و Change Data Capture. هیچ‌کدام در ۲۰۰۵ وجود ندارند. مثلاً ستون date باید به datetime یا smalldatetime تبدیل شود.

گام ۲: اصلاح مسیر فایل‌ها در اسکریپت

این نکتهٔ متن اصلی کاملاً درست است و مهم‌ترین جایی است که کار خراب می‌شود. اگر اسکریپت شامل دستور CREATE DATABASE باشد، مسیر فایل‌ها همان مسیر نصب نسخهٔ ۲۰۰۸ است و روی سرور ۲۰۰۵ وجود ندارد؛ و اگر هر دو نمونه روی یک ماشین یا یک استوریج مشترک باشند، مسیر تولیدشده به فایل زندهٔ دیتابیس ۲۰۰۸ اشاره می‌کند. SQL Server روی فایل موجود بازنویسی نمی‌کند و دستور با خطا متوقف می‌شود، اما همین کافی است که کار نیمه‌کاره بماند. فایل .sql را با یک ویرایشگر متن باز کنید و بخش FILENAME را اصلاح کنید:

-- مسیری که SSMS 2008 تولید می‌کند:
FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAMyDB.mdf'

-- مسیر معادل روی یک نمونهٔ SQL Server 2005:
FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataMyDB.mdf'

عدد انتهای پوشهٔ MSSQL.1 به ترتیب نصب نمونه‌ها بستگی دارد؛ مسیر واقعی سرور خودتان را با کوئری زیر بگیرید و از همان استفاده کنید:

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('master');

ساده‌ترین راه جایگزین: دیتابیس خالی را دستی روی ۲۰۰۵ بسازید، بلوک CREATE DATABASE را از اسکریپت حذف کنید و در ابتدای فایل USE [MyDB]; بگذارید.

گام ۳: اجرای اسکریپت روی سرور ۲۰۰۵

ابتدا Login های سطح سرور را روی ۲۰۰۵ بسازید؛ Generate Scripts فقط اشیای داخل دیتابیس را بیرون می‌دهد و Login ها جزو آن نیستند. سپس اسکریپت را اجرا کنید. اگر فایل بزرگ است، به‌جای SSMS از sqlcmd استفاده کنید چون SSMS روی فایل‌های چندصد مگابایتی دچار کمبود حافظه می‌شود:

sqlcmd -S SERVER2005INSTANCE -d MyDB -E -i C:scriptsMyDB.sql -o C:scriptsresult.txt

سوئیچ -E یعنی احراز هویت ویندوز؛ برای احراز هویت SQL از -U و -P استفاده کنید. اگر اسکریپت هنوز خودش CREATE DATABASE را دارد، به‌جای -d MyDB از -d master استفاده کنید، وگرنه اتصال به دیتابیسی که هنوز ساخته نشده شکست می‌خورد. فایل result.txt را بخوانید و مطمئن شوید هیچ خطایی ثبت نشده است.

در این مرحله همان چیزی را دارید که متن اصلی توصیف کرده بود: یک کپی از دیتابیس ۲۰۰۸ با تمام Object ها روی ۲۰۰۵، که جدول‌هایش خالی است.

گام ۴: انتقال داده‌ها

سه گزینهٔ عملی وجود دارد:

• SQL Server Import and Export Wizard — مناسب‌ترین گزینه برای اغلب موارد. مبدأ را نمونهٔ ۲۰۰۸ و مقصد را نمونهٔ ۲۰۰۵ بگذارید، «Copy data from one or more tables or views» را انتخاب کنید و در بخش Edit Mappings حتماً گزینهٔ Enable identity insert را تیک بزنید، وگرنه مقادیر ستون‌های IDENTITY دوباره تولید می‌شوند و کلیدهای خارجی به هم می‌ریزند.
• اسکریپت داده — همان گزینهٔ Schema and data در گام ۱ که دستورهای INSERT تولید می‌کند. برای دیتابیس‌های کوچک عالی است، برای جدول‌های میلیونی بسیار کند.
• bcp — برای جدول‌های خیلی بزرگ. برای انتقال بین دو نسخهٔ متفاوت، قالب کاراکتری (-c) امن‌تر از قالب Native است. سوئیچ -E در مرحلهٔ ورود، مقادیر IDENTITY اصلی را حفظ می‌کند. اگر داده‌های Unicode دارید به‌جای -c از -w استفاده کنید تا کاراکترهای فارسی خراب نشوند.

bcp "SELECT * FROM MyDB.dbo.Orders" queryout C:tempOrders.dat -S SERVER2008 -T -w
bcp MyDB.dbo.Orders in C:tempOrders.dat -S SERVER2005 -T -w -E

چون جدول‌ها از قبل با کلید خارجی و Trigger ساخته شده‌اند، ترتیب درج مهم می‌شود. ساده‌ترین راه، غیرفعال کردن موقت محدودیت‌ها و Trigger ها فقط روی دیتابیس تازه‌ساختهٔ مقصد است. رویهٔ sp_MSforeachtable مستندنشده است اما در ۲۰۰۵ و ۲۰۰۸ کار می‌کند، و روی هر جدول دیتابیسِ جاری اجرا می‌شود؛ پس خط USE را هرگز حذف نکنید و این بلوک را روی هیچ دیتابیس در حال سرویس‌دهی اجرا نکنید:

-- قبل از انتقال داده — فقط روی سرور 2005 و فقط روی دیتابیس مقصد
USE [MyDB];
GO
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL';
GO

-- بعد از انتقال داده — همین‌جا و بلافاصله
USE [MyDB];
GO
EXEC sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL';
GO

اگر Recovery Model دیتابیس مقصد FULL است، در طول انتقال می‌توانید آن را موقتاً روی SIMPLE بگذارید تا فایل Log بی‌رویه بزرگ نشود. توجه داشته باشید که این کار زنجیرهٔ بک‌آپ Log را قطع می‌کند و تا وقتی بک‌آپ کامل بعدی گرفته نشود، بازیابی نقطه‌ای ممکن نیست؛ پس این کار را فقط روی دیتابیسی انجام دهید که هنوز به کاربران واگذار نشده و در Log Shipping یا Mirroring شرکت نمی‌کند. بعد از پایان کار Recovery Model را به حالت قبل برگردانید و بلافاصله یک بک‌آپ کامل بگیرید.

چطور مطمئن شویم انتقال درست انجام شده است

«خطا نداد» معیار کافی نیست. این سه بررسی را روی هر دو سرور اجرا کنید و خروجی‌ها را مقایسه کنید. هر سه کوئری دیتابیس‌محور هستند، پس اول USE [MyDB]; را اجرا کنید.

تعداد اشیا بر اساس نوع:

USE [MyDB];
SELECT type_desc, COUNT(*) AS object_count
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc
ORDER BY type_desc;

تعداد رکورد هر جدول:

USE [MyDB];
SELECT s.name AS schema_name, t.name AS table_name, SUM(p.rows) AS row_count
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY s.name, t.name;

و اعتبارسنجی محدودیت‌هایی که موقتاً غیرفعال کرده بودید — این کوئری باید خروجی خالی برگرداند:

USE [MyDB];
SELECT name, is_not_trusted
FROM sys.foreign_keys
WHERE is_not_trusted = 1;

در پایان، آمار ایندکس‌ها را روی دیتابیس مقصد به‌روز کنید تا Query Plan ها درست ساخته شوند (این رویه هم روی دیتابیس جاری کار می‌کند):

USE [MyDB];
EXEC sp_updatestats;

خطاهای رایج و راه‌حل

• کاربر بدون Login (Orphaned User) — بعد از انتقال، کاربران دیتابیس به Login های سرور جدید وصل نیستند و اپلیکیشن خطای Login failed می‌گیرد. راه‌حل، که در خود SQL Server 2005 هم پشتیبانی می‌شود: ALTER USER [AppUser] WITH LOGIN = [AppUser];. رویهٔ قدیمی sp_change_users_login 'Auto_Fix', 'AppUser' هم در ۲۰۰۵ و ۲۰۰۸ کار می‌کند، اما مایکروسافت آن را منسوخ اعلام کرده؛ در کد جدید از ALTER USER استفاده کنید.
• اسکریپت با وجود انتخاب Server Version برابر ۲۰۰۵ خطا می‌دهد — یعنی شیئی از قابلیتی استفاده می‌کند که در ۲۰۰۵ وجود ندارد. متن خطا نام شیء را می‌دهد؛ همان را دستی بازنویسی کنید.
• مقادیر ستون IDENTITY عوض شده‌اند — Enable identity insert یا سوئیچ -E در bcp فراموش شده. جدول‌های سرور مقصد (۲۰۰۵) را خالی کنید و داده را دوباره منتقل کنید؛ به سرور مبدأ ۲۰۰۸ دست نزنید.
• خطای نقض کلید خارجی هنگام درج — یا ترتیب جدول‌ها را رعایت کنید یا محدودیت‌ها را طبق بالا موقتاً غیرفعال کنید.
• مواردی که اصلاً در اسکریپت دیتابیس نمی‌آیند — Job های SQL Server Agent، Linked Server ها، Assembly های CLR، کاتالوگ‌های Full-Text و اشیای Service Broker باید جداگانه روی سرور ۲۰۰۵ ساخته شوند.

چه زمانی این کار را نکنید

انتقال به نسخهٔ پایین‌تر همیشه با از دست رفتن چیزی همراه است و باید آخرین گزینه باشد. اگر دلیل کار فقط این است که سرور مقصد قدیمی است، بهتر است سرور مقصد را ارتقا دهید تا دیتابیس را پایین بیاورید.

ضمناً هر دو نسخه امروز خارج از پشتیبانی مایکروسافت هستند: پشتیبانی توسعه‌یافتهٔ SQL Server 2005 در ۱۲ آوریل ۲۰۱۶ و SQL Server 2008 و ۲۰۰۸ R2 در ۹ ژوئیهٔ ۲۰۱۹ پایان یافته است. یعنی هیچ‌کدام دیگر به‌روزرسانی امنیتی نمی‌گیرند و بردن یک دیتابیس از ۲۰۰۸ به ۲۰۰۵ عملاً آن را به نسخه‌ای می‌برد که سه سال زودتر از مبدأ منسوخ شده است. اگر اپلیکیشن شما واقعاً به رفتار ۲۰۰۵ وابسته است، گزینهٔ بهتر این است که دیتابیس روی یک نسخهٔ پشتیبانی‌شده بماند و فقط Compatibility Level پایین‌تری بگیرد؛ توجه کنید که هر نسخهٔ SQL Server حداقلِ Compatibility Level مشخصی را پشتیبانی می‌کند و مقدار ۹۰ در نسخه‌های امروزی دیگر پذیرفته نمی‌شود.

همین قاعده برای نسخه‌های امروزی هم برقرار است

محدودیت «مسیر برگشت وجود ندارد» مخصوص ۲۰۰۸ و ۲۰۰۵ نیست؛ دقیقاً همین رفتار بین ۲۰۱۹ و ۲۰۱۶، یا ۲۰۲۲ و ۲۰۱۹ هم دیده می‌شود. بک‌آپ نسخهٔ بالاتر روی نسخهٔ پایین‌تر Restore نمی‌شود و پیام خطا هم همان است، فقط شماره‌ها فرق می‌کنند. بنابراین همین روش — Generate Scripts با تنظیم Script for Server Version روی نسخهٔ مقصد، به‌علاوهٔ انتقال جداگانهٔ داده — راهکار استاندارد برای هر جفت نسخه است. در نسخه‌های جدید SSMS همان گزینه در همان مسیر Advanced قرار دارد.

یک گزینهٔ دوم هم وجود دارد: Export Data-tier Application که فایل .bacpac شامل ساختار و داده می‌سازد و روی نمونهٔ قدیمی‌تر قابل Import است. برخلاف تصور رایج، این مسیر برای مقصد ۲۰۰۵ هم به‌کلی بسته نیست: طبق مستندات مایکروسافت، عملیات Export و Import روی SQL Server 2005 SP4 و بالاتر و SQL Server 2008 SP2 و بالاتر پشتیبانی می‌شود، اما ابزار سمت کلاینتِ لازم برای آن (DAC Framework) از SQL Server 2008 R2 SP1 به بعد این دو عملیات را دارد — نسخهٔ همراه SSMS 2008 R2 اولیه هنوز Export/Import نداشت. در عمل برای این سناریو دو محدودیت جدی باقی می‌ماند: فهرست اشیای پشتیبانی‌شده در DAC باریک است (CLR، Service Broker، Full-Text، پارتیشن‌بندی و Trigger های DDL در آن نیستند) و اگر دیتابیس مبدأ حتی یکی از این‌ها را داشته باشد Export اصلاً انجام نمی‌شود. به همین دلیل برای یک دیتابیس واقعی و پرکاربرد، روش Generate Scripts همچنان قابل‌اعتمادترین مسیر است.

در هر حالت، قبل از شروع یک بک‌آپ کامل از دیتابیس مبدأ بگیرید و صحت آن را با RESTORE VERIFYONLY بررسی کنید؛ هیچ‌یک از مراحل بالا برگشت‌پذیر نیست.