http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [194]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2888]
PostgreSQL [103]
기타정보 [150]
운영체제 [577]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Work Book :: MS SQL

 회원가입

mssql sqlcmd [ backup script ]
최길호 [LIST]   2015-07-05 12:21:27, 조회 : 7,029

C:\Users\Administrator>sqlcmd -S 192.168.0.155,1433 -Usa -Pmssqlpwd
1> select @@version;
2> go

sqlcmd
sqlcmd -A
sqlcmd -S 서버명\사용자명
sqlcmd -S dbsvr\sqlexpress -U user

-- recovery model 확인
SELECT name, recovery_model_desc FROM sys.databases;
sqlcmd -S 127.0.0.1,1433 -Q "SELECT (name + ': ' + recovery_model_desc) COLLATE DATABASE_DEFAULT FROM sys.databases"

-- FULL 로 변경
ALTER DATABASE model SET RECOVERY FULL ;
ALTER DATABASE model SET RECOVERY SIMPLE ;
go

-- 백업
sqlcmd -S 127.0.0.1,1433 -Q "BACKUP database model TO DISK='c:\backup\full.%date%.bak'" >> c:\backup\full.%date%.log
sqlcmd -S 127.0.0.1,1433 -Q "BACKUP LOG model TO DISK='c:\backup\full.%date%.bak'" >> c:\backup\full.%date%.log

-- select db_name
SELECT name from sys.databases;
-- dbname,filename,size
select name as DBName, filename,
  case groupid when 1 then 'Data' ELSE 'Log' END AS type,
  (size*8)/(1024.) AS SizeInMBs
from master..sysaltfiles;

-- describe table
select * from INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'database';
-- session
sp_who
sp_who 'active'
sp_who 'login_name'
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses

-- dbfile [ use dbname 관련 파일만 보임 ]
SELECT * FROM dbo.sysfiles

-- 3 line
select top(3) * from emp;
-- create table as select ...
select * into emp1 from emp;

-- begin tran 으로 transaction 시작 해야 rollback/commit 가능
1> rollback
2> go
메시지 3903, 수준 16, 상태 1, 서버 dbsvr\SQLEXPRESS, 줄 1
ROLLBACK TRANSACTION 요청에 해당하는 BEGIN TRANSACTION이 없습니다.
=> begin tran / DML / rollback | commit [ tran ]


exec sp_detach_db 'testdb'
go
exec sp_attach_db 'testdb','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb.mdf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb_log.LDF'

-- testdb 200M 제한없이 자동 10% 증가
alter database testdb modify file (name=testdb,size=200,maxsize=unlimited,filegrowth=10)
go
alter database testdb add file (name=testdb2, filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb2.mdf',size=2)
alter database testdb add log file (name=testdb2_log,filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testdb2.ldf',size=2)
alter database testdb remove file testdb2_log
alter database testdb modify name = dbtest
-- 2008 부터 지원 identify 자동증가 1부터 1씩 증가
create table test ( id int identify(1,1))

-- view 관련
create view vemp as select * from emp where DEPTNO=10
select * from vemp;
-- view script 확인
sp_helptext vemp
alter view vemp with encryption as select * from emp where DEPTNO=10
개체 'vemp'의 텍스트가 암호화되었습니다. [ sp_helptext 확인 불가 ]

--- 백업/복구
--- http://www.sqler.com/bColumn/700208
BACKUP DATABASE SW_TEST TO DISK = 'D:\SQL_BACKUP\SW_TEST.BAK'
GO
BACKUP DATABASe SW_TEST TO DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF1.BAK' WITH DIFFERENTIAL
GO
BACKUP DATABASe SW_TEST TO DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF2.BAK' WITH DIFFERENTIAL
GO
==
RESTORE DATABASE SW_TEST FROM DISK = 'D:\SQL_BACKUP\SW_TEST.BAK' WITH NORECOVERY, REPLACE
GO
RESTORE DATABASE SW_TEST FROM DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF1.BAK' WITH NORECOVERY, REPLACE
GO
RESTORE DATABASE SW_TEST FROM DISK = 'D:\SQL_BACKUP\SW_TEST_DIFF1.BAK' WITH RECOVERY
GO
-- 백업된 정보
-- http://www.sqler.com/bColumn/700217
SELECT
A.DATABASE_NAME, B.PHYSICAL_DEVICE_NAME,
CAST(A.BACKUP_SIZE/1000000 AS VARCHAR(14))+' '+'MB' AS BKSIZE,
CAST (DATEDIFF(SECOND,A.BACKUP_START_DATE , A.BACKUP_FINISH_DATE)AS VARCHAR(4))+' '+'SECONDS' AS RUNTIME,
A.BACKUP_START_DATE,
CASE A.[TYPE]
WHEN 'D' THEN 'FULL BACKUP'
WHEN 'I' THEN 'DIFFERENTIAL BACKUP'
WHEN 'L' THEN 'TRANSACTION LOG BACKUP'
END AS BACKUPTYPE,
A.SERVER_NAME,
A.RECOVERY_MODEL
FROM MSDB.DBO.BACKUPSET AS A
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS B
ON A.MEDIA_SET_ID = B.MEDIA_SET_ID
WHERE A.DATABASE_NAME = 'SW_TEST'
ORDER BY DATABASE_NAME, BACKUP_START_DATE, BACKUP_FINISH_DATE


  LIST

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
06/29
[ORACLE]
DDE: Problem Key 'ORA 600 [133....
by 최길호
06/25
[ORACLE]
Replication may not be valid i....
by 최길호
06/22
[운영체제]
CentOS7 홈페이지 Migration 메모.
by 최길호