4 Loop Web Development
  • Services
  • Information
  • Contact
  • Services
  • Information
  • Contact

Move SQL Server 2016 (13.0.1601) to SQL Server 2012 (11.0.5388)

I was having issues doing a restore from an newer sql server backupto an older sql server so was able to get data moved the following way.

  1. Open Microsoft SQL Server Mangement Studio (MSSMS)
  2. Right click on database you are wanting to export, select Tasks > Generate Scripts...
  3. In 'Generate and Publish Scripts' wizard,
  4. Introduction > Click 'Next'
  5. Choose Objects > Select radio 'Script entire database and all database objects'
  6. Set Scripting Options > Output Type = 'Save scripts to a specific location'. Set 'Save to file' and set 'Files to generate' to 'Single file per object'. Set 'Directory name' to where you will remember.
  7. Go back up, click on 'Advanced' button and in 'Advanced Scripting Options' change to the following:
    - Script for Server Version = SQL Server 2012
    - Types of data to script = Schema and data
    - Under Table/View Options you might want to set 'Script Full-Text Indexes' and 'Script Triggers' to True if you want to grab that information - Click 'OK' when finished
  8. Click 'Next' when ready
  9. Summary - Click 'Next' to start creating files
  10. Save or Publish Scripts - Click 'Finish' when complete
  11. Since my other database is on a different server, I zip up the .sql files created and move to new server. In this instance I will copy and unzip to the following folder: E:\backups\sql_v2012-files
  12. On server you copied files too, create a file called createbatwithSQLCMDlines.bat file (or whatever you want to call it) and paste the following text inside and modify to your system values. Save when finished.
    REM read folder of .sql files and create sql statements
    REM E:\backups\sql_v2012-files is the location where your .sql files are
    REM change [INSTANCE] and [DBNAME] with what you have
    for /f %%f in ('dir /b E:\backups\sql_v2012-files') do (
      SET "line=%%f"
      echo sqlcmd -S [INSTANCE]	-i E:\backups\sql_v2012-files\%%f -d [DBNAME] -o c:\!line:.sql=.txt! >> c:\runsqlnow.bat
    REM output will be in same folder, run output .bat as admin and will load .sql into database
  13. I like to manually create the database, same name as what I used in [DBNAME] in code above.
  14. Right click and 'Run as administrator'. This should create a file called 'runsqlnow.bat' in the c: folder which if you edit will see all your .sql files and can run this to import data into new database.
  15. I recreate the database user because sometimes it doesn't import properly.
    Databases > [DBName] > Security > Users > [Username] Properties
    -General: Default schema = dbo
    -Owned Schemes: nothing checked
    -Membership: db_datareader, db_datawriter, and db_owner checked
    -Securables: nothing
    -Extended Properties: nothing

    Security > Logins > [Username] Properties
    ---SQL Server authentication
    ---Unchecked: Specify old password, Enforce password policy
    ---Default database: [DBName]
    -Server Roles: public
    -User Mapping: [DBName]: db_datareader, db_datawriter, public
    -Permissions: Connect SQL, sa, Grant
    -Status: Grant and Enabled selected

When all finished I would run the following sql on both database instances and put into excel file side by side and should match. If no, something didn't work.


If want to search for a field called 'Phone' you can use the following sql:

select schema_name(tab.schema_id) as schema_name,
    tab.name as table_name, 
    col.name as column_name, 
    t.name as data_type,    
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
where col.name = 'Phone'
order by schema_name,