|
IOTN :: Work Book :: MS SQL 
 |
mssql sqlcmd [ backup script ]
|
ÃÖ±æÈ£
[LIST]
|
2015-07-05 12:21:27, Á¶È¸ : 7,153 |

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-2025 Zeroboard / skin by ÃÖ±æÈ£(gilho.kr@gmail.com)
|
|
|