Backup and Restore System Database

************************ Backup *****************************************
- backup Database to Disk

backup database master to disk = 'path'

- Backup Databse to Backup device

Create backup device

sp_addumpdevice @physicalType , @logicalname , 'Store Path'

Backup Database to device

backup database master to @devicename

************************ Restore *****************************************

1. Install the new SQL Server Instance
2. Start SQL Server Instance
3. Install Service Pack and hotfix
4. Stop SQL Agent
5. Start the SQL Server in single-user mode
6. Use SQLCMD log on to SQL Server instance with system administrator account
7. Restore the master database with excute command

Restore database [Master] from disk = 'path'

Database Miroring with T-SQL

This example shows all the stages required to create a database mirroring session with a witness using Windows Authentication. The examples in this topic use Transact-SQL. Note that as an alternative to using Transact-SQL steps, you can use the Configure Database Mirroring Security Wizard for database mirroring setup. For more information, see Managing Database Mirroring (SQL Server Management Studio).
Prerequisite
________________________________________
The example uses the AdventureWorks sample database, which uses the simple recovery model by default. To use database mirroring with this database, you must alter it to use the full recovery model. To do this in Transact-SQL, use the ALTER DATABASE statement, as follows:

USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
For information on changing the recovery model in SQL Server Management Studio, see How to: View or Change the Recovery Model of a Database (SQL Server Management Studio).
Example
________________________________________
In this example, the two partners and the witness are the default server instances on three computer systems. The three server instances run the same Windows domain, but the user account is different for the example's witness server instance.
The following table summarizes the values used in this example.
Initial mirroring role Host system Domain user account
Principal PARTNERHOST1 \
Mirror PARTNERHOST5 \
Witness WITNESSHOST4 \
1. Create an endpoint on the principal server instance (default instance on PARTNERHOST1).

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
--Partners under same domain user; login already exists in master.
--Create a login for the witness server instance,
--which is running as Somedomain\witnessuser:
USE master ;
GO
CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;
GO
-- Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];
GO
2. Create an endpoint on the mirror server instance (default instance on PARTNERHOST5).

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO
--Partners under same domain user; login already exists in master.
--Create a login for the witness server instance,
--which is running as Somedomain\witnessuser:
USE master ;
GO
CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ;
GO
--Grant connect permissions on endpoint to login account of witness.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser];
GO
3. Create an endpoint on the witness server instance (default instance on WITNESSHOST4).

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO
--Create a login for the partner server instances,
--which are both running as Mydomain\dbousername:
USE master ;
GO
CREATE LOGIN [Mydomain\dbousername] FROM WINDOWS ;
GO
--Grant connect permissions on endpoint to login account of partners.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername];
GO
4. Create the mirror database. For more information, see How to: Prepare a Mirror Database for Mirroring (Transact-SQL).
5. On the mirror server instance on PARTNERHOST5, set the server instance on PARTNERHOST1 as the partner (making it the initial principal server instance).

ALTER DATABASE AdventureWorks
SET PARTNER =
'TCP://PARTNERHOST1.COM:7022'
GO
6. On the principal server instance on PARTNERHOST1, set the server instance on PARTNERHOST5 as the partner (making it the initial mirror server instance).

ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://PARTNERHOST5.COM:7022'
GO
7. On the principal server, set the witness (which is on WITNESSHOST4).

ALTER DATABASE AdventureWorks
SET WITNESS =
'TCP://WITNESSHOST4.COM:7022'
GO


reference : http://msdn.microsft.com/

Switch Recovery Model with T-SQL

Full recovery model

alter database [databasename] set recovery full

Bulk log recovery model

alter database [databasename] set recovery bulk_logged

Simple recovery model

alter database [databasename] set recovery simple

Backup Database with T-SQL to Backup device

Step by Step

1. Create Backup Device
exec sp_addumpdevice 'disk','adventureworks2008backup','d:\backupdb\aw2008.bak'

2. verify backup device
select * from sysdevices

3. Remove Backup device
sp_dropdevice @logicalname = 'adventureworks2008bakup'

4. backup database to device
backup database adventureworks to adventureworks2008backup with init ,stats = 10

5. Verify Backup

restore verifyonly from adventureworks2008backup

Add user with T-SQL

Syntax

sp_adduser [ @loginame = ] 'login'
[ , [ @name_in_db = ] 'user' ]
[ , [ @grpname = ] 'role' ]

Sample

EXEC sp_adduser 'Vidur', 'Vidur', 'Recruiting'

Basic backup and Restore Database with T-SQL

******* Backup Database *******

Backup To Disk

backup Database Databasename to Disk= 'File Destination path'

Backup To Device

sp_adddumpdevice @devicename

Backup Database Databasename to device

******* Restore Database ******

Restore From Disk
Restore database Databasename from disk= 'File Destination path'
Restore From Device
Restore database Databasename from device = Devicename

Database Snapshot (Transact-SQL)

******* Create Database Snapshot (T-SQL)*******

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO

******* View Database Snapshot (T-SQL) *******

slect * from sys.databases

******* Revert Database Snapshot (T-SQL) *******

USE master;
-- Reverting AdventureWorks to AdventureWorks_dbss1800
RESTORE DATABASE AdventureWorks from
DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';
GO

******* Drop Database Snapshot (T-SQL) *******

DROP DATABASE SalesSnapshot0600

sqlcmd Utility

sqlcmd.exe is one of msdn utility, that let you enter sql statements
sqlcmd.exe เป็นเครื่องมือหนึ่งของ MSDN ที่ไว้สำหรับสั่งคำสั่ง sql ผ่าน command line

sqlcmd.exe is installed in
โดยปกติแล้วจะถูกติดตั้งไว้ที่ ลองตรวจสอบดูว่าได้ลงไว้แล้วหรือยัง
C:\Program Files\Microsoft SQL Server\90\Tools\binn

you can download and install from microsoft
ถ้ายังไม่มีสามารถดาวน์โหลดมาติดตั้งเฉพาะโปรแกรมนี้ได้ดังนี้

Download
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
install sqlncli.exe and follow with sqlncli.exe
เข้าไปที่เวบสำหรับดาวน์โหลดของ ไมโครซอฟท์ แล้วเลือกดาวน์โหลด เฉพาะ sqlncli.exe และ sqlncli.exe

Howto use for backup
การใช้งาน
1. run sqlcmd.exe at command prompt
สั่ง sqlcmd จะเจอ 1> เพื่อรอชุดคำสั่ง

2. use the following command
สามารถสั่งคำสั่งเป็นชุดได้ เมื่อต้องการให้ทำงานใช้คำสั่ง go
1> backup database YOURDATABASENAME
2> to disk=’C:\BACKUPNAME’
3> go

3. when command complete will return result like this
เมื่อทำงานเสร็จแล้วจะมีข้อความมาแจ้ง และไฟล์ที่เราได้ก็จะอยู่ตามที่เราตั้งไว้ในบรรทัดที่ 2
Processed 35464 pages for database ‘YOURDATABASENAME’, file ‘db’ on file 1.
Processed 1 pages for database ‘YOURDATABASENAME’, file ‘db_Log’ on file 1.
BACKUP DATABASE successfully processed 35465 pages in 55.686 seconds (5.217 MB/s
ec).

4. working with batch script
คำสั่งในการ backup สามารถนำไปใส่ใน batch ได้ ดังนี้
4.1 use sqlcmd togather with sql text file
เขียน query เซฟไว้เป็น text file แล้วสั่ง run ผ่าน sqlcmd

query.txt
backup database YOURDATABASENAME to disk=’C:\BACKUPNAME’

use following command at command prompt
เวลาเรียกใช้งานก็ใช้ดังนี้

sqlcmd -i query.txt

4.2 Write All script with one Command
เขียนทั้งหมดผ่าน batch script เลย
sqlcmd -Q "backup database YOURDATABASENAME to disk=’C:\BACKUPNAME’

For more Information please follow these Links
รายละเอียดเพิ่มเติมดูได้จากเวบของไมโครซอฟ์ทเอง

Remark
sqlcmd
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -l login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f <> | i: <> [ < , o: <> ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format]]
[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit]
[ -? show syntax summary ]