My SQL Server 2005 host died, but the database related LUNS were on a SAN and still exist. Is there a way to use my SQL Server 2012 host to recover that database or import it in some way?
Sql-server – Lost the SQL Server 2005 host, SQL Server 2005 database import into SQL Server 2012
sql serversql-server-2005sql-server-2012
Related Solutions
I can't believe I didn't actually google the exact error message. There's a lesson to be learned there. Upon doing so, I learned that similar errors occur in other applications. The cause is non-ASCII codes. I was exporting from SQL in Unicode.
Here is the full solution: In SQL:
- Right-click on the desired db, choose Tasks > Generate Scripts
- Select the desired database, next
- Leave the default options (I've tried a few things, none worked), next
- Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
- Select all tables (or 4 tables, it doesn't matter)
- Choose "Script to file", enter a file name
- Under Save as: choose ANSI text (Unicode breaks the crap out of ER/Studio)
- Finish
Then import into ER/Studio. My import has been running for 2 hours so far.
Next up, trying to figure out how to make an ordered list on this site. I'm so full of fail. :)
You can follow any method below :
Note: If you are using any new features like new data types, etc then you have to test out as it will throw errors.
METHOD 1: Using Native Tools
Script out the database SCHEMA_ONLY and recreate an empty database on the destination server. Below are the screenshots :
Use BCP OUT and BULK INSERT to insert data.
Below is the script that will help you with Part 2.
/************************************************************************************************************************************************
Author : KIN SHAH *********************************************************************************************************************
Purpose : Move data from one server to another*********************************************************************************************
DATE : 05-28-2013 *********************************************************************************************************************
Version : 1.0.0 *************************************************************************************************************************
RDBMS : MS SQL Server 2008R2 and 2012 *************************************************************************************************
*************************************************************************************************************************************************/
-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat
select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
+ QUOTENAME(DB_NAME())+ '.' -- Current Database
+ QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
+ QUOTENAME(name)
+ ' out D:\BCP_OUT\' -- Path where BCP out files will be stored
+ REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
+ REPLACE(name,' ','')
+ '.dat -T -E -SSERVERNAME\INSTANCE -n' -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams' -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude' -- Optional to exclude any schema
order by schema_name(schema_id)
--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.
declare @Destdbname sysname
set @Destdbname = 'destination_database_Name' -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT ' -- Remember Tables **must** be present on destination Database
+ QUOTENAME(@Destdbname)+ '.'
+ QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
+ QUOTENAME(name)
+ ' from ''D:\BCP_OUT\' -- Change here for bcp out path
+ REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
+ REPLACE(name,' ','')
+'.dat''
with (
KEEPIDENTITY,
DATAFILETYPE = ''native'',
TABLOCK
)' + char(10)
+ 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+ REPLACE(name,' ','')+' is done... '''+ char(10)+'go'
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams' -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude' -- Optional to exclude any schema
order by schema_name(schema_id)
Method 2 : Using Third Party Tools
Create a blank database on destination server. Use Redgate's schema compare and data compare to create and load data into the destination server.
Note: I have used Redgate's schema and data compare and they are the best tools for such type of task and hence if you are using 3rd party tools, then my recommendation would be Redgate.
Best Answer
The absolute best way would be to restore onto 2012 from backups if you have them, but you can also attach the databases from the SAN files onto your 2012 instance.
Once you do that, be sure to run DBCC CHECKDB to make sure all your data is intact.