• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15
  • Last Modified:

T-SQL Scripts for DB migration

I am looking for scripts for below requirements:

1. How quickly attach 100+ databases in sql 2016?
2. How quickly change 100+ databases compatibility level (from 100 to 130) in sql 2016?

The exact requirement is that we are migrating sql server 2008 to 2016.
0
Vijay
Asked:
Vijay
  • 3
1 Solution
 
Nitin SontakkeDeveloperCommented:
Very simple, if you know already how to use meta data or use scripts to generate scripts. Too lazy to to type everything here. Pick-up a hint if you can and experiment. The best approach.

declare @dynamicSQL nvarchar(max) = '';

select @dynamicSQL += 'execute sp_attach_db ''' + [name] + ''';
'
from sys.databases 
where 1 = 1
  and [database_id] > 4

select @dynamicSQL;

Open in new window

0
 
Nitin SontakkeDeveloperCommented:
Incidently, sp_attach_db is presumably now deprecated, use this instead:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database
0
 
Nitin SontakkeDeveloperCommented:
declare @dynamicSQL nvarchar(max) = '';

select @dynamicSQL += 'alter database [' + [name] + ']
  set compatibility_level = 130;

'
from sys.databases 
where 1 = 1
  and [database_id] > 4

select @dynamicSQL;

Open in new window

0
 
VENKAT KOKULLASQL Server DBACommented:
0
 
VijayAuthor Commented:
Thank you Venkat.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now