0

I am working on migrating databases from a 2017 SQL server to a 2016 version. The databases I am working with are hundreds of gigabytes so I am using generate script along with sqlcmd to restore.

However, when I restore the database on the new server it creates the tables and files them under the Master db rather than recreating the entire saved database. I want the exact same DB to be restored on the new server. Can anyone help me figure out what I am doing wrong?

Steps taken:

  1. Generate Script - Include Data and Schema
  2. Open cmpt prompt and enter: sqlcmd -S (servername) -i (file location)

The tables populate under the Master database instead of restoring the original database standalone.

Patrick
  • 1
  • 1
    `sqlcmd -S servername -d db_name -i file_location` – squillman May 18 '22 at 21:28
  • 1
    This could happen if the `CREATE DATABASE` is either missing from the script, or fails for some reason -- the script won't include any kind of error checking, so if the `CREATE DATABASE` and `USE` fail it will just happily continue in the original DB. As a matter of safety, consider doing the `CREATE DATABASE` step entirely separately, and then invoking `sqlcmd` with `-d` to point at the database, which *will* fail if it doesn't exist. – Jeroen Mostert May 18 '22 at 21:28
  • 1
    Also, as a matter of practicality, if your databases are "hundreds of gigabytes" then don't script the data, this is madly inefficient. Script the schemas only, and then use either Management Studio's built in data import/export wizard or (if not available or not desirable) use `bcp -n` to import and export data in native format (or `-N` if you've set up your servers with different collations, for some reason). Both the resulting file sizes and the time needed to transfer data should decrease dramatically, the only drawback is needing a little loop for the individual tables. – Jeroen Mostert May 18 '22 at 21:48

0 Answers0