|
|
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
|
|
|
|
|
Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
|
|
|
06/29 