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' and Save to file with 'Single file per object'. Set Directory name to where you will remember.
  7. Click '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
  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"
      SETLOCAL ENABLEDELAYEDEXPANSION
      echo sqlcmd -S [INSTANCE]	-i E:\backups\sql_v2012-files\%%f -d [DBNAME] -o c:\!line:.sql=.txt! >> c:\runsqlnow.bat
      ENDLOCAL
    )
    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.