This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
T-SQL Generate attach and detach database script for all User Databases On a SQL Instance
/* SQL Server: T-SQL Script To Generate Attach and Detach Commands for all User Databases On a SQL Instance */
-- Overview: SQL Scripts that generates commands for your attach and detach stored procedures for all of your user databases
-- Usage: Change the 'SET @DetachOrAttach' parameter to a '0' (attach) or '1' (detach) value to generate your T-SQL commands
-- Environments: SQL Server 2008 / 2012 /2014/2014
USE [master]; | |
GO | |
DECLARE @database NVARCHAR(200) , | |
@cmd NVARCHAR(1000) , | |
@detach_cmd NVARCHAR(4000) , | |
@attach_cmd NVARCHAR(4000) , | |
@file NVARCHAR(1000) , | |
@i INT , | |
@DetachOrAttach BIT; | |
SET @DetachOrAttach = 0; -- Change this to '0' for sp_attach_db commands; or '1' for sp_detach_db commands | |
-- 0 Generates Attach Script | |
-- 1 Generates Detach Script | |
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY | |
FOR | |
SELECT RTRIM(LTRIM([name])) | |
FROM sys.databases | |
WHERE database_id > 4; | |
-- No system databases | |
OPEN dbname_cur | |
FETCH NEXT FROM dbname_cur INTO @database | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SELECT @i = 1; | |
SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10) | |
+ 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10); | |
-- Change skip checks to false if you want to update statistics before you detach. | |
SET @detach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10) | |
+ 'EXEC sp_detach_db @dbname = ''' + @database | |
+ ''' , @skipchecks = ''true'';' + CHAR(10); | |
-- Get a list of files for the database | |
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY | |
FOR | |
SELECT physical_name | |
FROM sys.master_files | |
WHERE database_id = DB_ID(@database) | |
ORDER BY [file_id]; | |
OPEN dbfiles_cur | |
FETCH NEXT FROM dbfiles_cur INTO @file | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @attach_cmd = @attach_cmd + ' ,@filename' | |
+ CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + '''' | |
+ CHAR(10); | |
SET @i = @i + 1; | |
FETCH NEXT FROM dbfiles_cur INTO @file | |
END | |
CLOSE dbfiles_cur; | |
DEALLOCATE dbfiles_cur; | |
IF ( @DetachOrAttach = 0 ) | |
BEGIN | |
-- Output attach script | |
PRINT @attach_cmd; | |
END | |
ELSE -- Output detach script | |
PRINT @detach_cmd; | |
FETCH NEXT FROM dbname_cur INTO @database | |
END | |
CLOSE dbname_cur; | |
DEALLOCATE dbname_cur; |
How to move system databases from one location to another location in SQL server
Move tempdb:
1) first we have to know the location of tempdb data file and log file location by using below query
use tempdb
go
exec sp_helpfile
go
2) run the below query to save the sql server new location of tempdb data file and log file path
alter database tempdb modify file (name=tempdev, filename='E:\sysdbs\mdf\tempdb.mdf')
go
alter database tempdb modify file (name=templog, filename='E:\sysdbs\ldf\templog.ldf')
go
after execute the above query it shows message like the sql server save your catalog but you must restart the sql server
3) stop the sql server
4) start the sql server
5)verify the path for tempdb data file and log file by using below query
exec sp_helpfile
go
6) whenever restart the sql server new tempdb will be created so automatically the new tmpdb will created in new location goto old location and delete the old tempdb files
move model :
1) first we have to know the location of tempdb data file and log file location by using below query
use model
go
exec sp_helpfile
go
2)run the below query to save the sql server new location of model data file and log file path
alter database model modify file (name=modeldev, filename='E:\sysdbs\mdf\model.mdf')
go
alter database model modify file (name=modellog, filename='E:\sysdbs\ldf\modellog.ldf')
go
after execute the above query it shows message like the sql server save your catalog but you must restart the sql server
3) stop the sql server
4)move the model databse data file and log file from old location to new location
5) start the sql server
6)verify the path for model data file and log file by using below query
exec sp_helpfile
go
move msdb :
1) first we have to know the location of msdb data file and log file location by using below query
use msdb
go
exec sp_helpfile
go
2)run the below query to save the sql server new location of msdb data file and log file path
alter database msdb modify file (name=msdbdata, filename='E:\sysdbs\mdf\msdbdata.mdf')
go
alter database msdb modify file (name=msdblog, filename='E:\sysdbs\ldf\msdblog.ldf')
go
after execute the above query it shows message like the sql server save your catalog but you must restart the sql server
3) stop the sql server
4)move the msdb data file and log file from old location to new location
5) start the sql server
6)verify the path for model data file and log file by using below query
exec sp_helpfile
go
move master :
1) copy the startup paramaeter(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)
-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2) stop the sql server
3) move the master data file and log file from old location to new location
4)paste the startup parameter as a new location of master data fil;e and log file
(sql server configuration manager---sql server services---sql server(mssqlserver)---properties---advanced---startup parameter)
-dE:\sysdbs\mdf\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lE:\sysdbs\ldf\mastlog.ldf
apply ok
5)it shows one message like system save the ur request but you must restart the sql server to apply these rules
6) start the sql server
7)if run sql server there is no issues if sql server will not work check the startup parameters(startup parameters are very senstive it doesnt have single extra collan or space etc)
8)check the new path of master database data file and log file by using below query
exec sp_helpfile
T-SQL TRANSACT STRUCTURED QUERY LANGUAGE
T-SQL
TRANSACT STRUCTURED QUERY LANGUAGE
This is a 4th generated
intermediate language between user and SQL Server. Whenever user wants to
interact with SQL Server, he has to interact with SQL Server through T-SQL. It
includes the following Sub Languages
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
Things to Observe:
- While writing the Queries using T-SQL
in SQL Server Management Studio
we need not to follow any particular case. Because T-SQL is case insensitive language.
- After writing the Query, we need
to select that query using either mouse or keyboard.
- Now Press F5 ( Execute Key).
- Then the results are displayed in
a separate window called Result
window or Result Pane.
- Use Ctrl+R to Hide/Show the Result window or Result Pane.
- Use F8 for Object Explorer
Note:
- SQL SERVER can handle nearly 32767
Databases
- Each Database can handle nearly 2
billion Database Objects.
- Each Table can handle nearly 1024
columns
- Each Table can handle nearly 1
million Rows.
Subscribe to:
Posts (Atom)