SQL SERVER2014 Database Backup restore

Create a database

Create DATABASE Testdatabase

To switch databases:

Use Testdatabase

Use master

Backing up SQL Server

---to create a device that backs up data

Use master

EXEC sp_addumpdevice ' disk ', ' testBack5 ', ' E:\sqlservertest.dat '

When you delete a device that backs up data, the backup file is also deleted:

Sp_dropdevice ' TestBack5 '

---start Backup

Backup Database DbName to TESTBACK5

Note: Backup failed, testback device, cannot be opened

The reason is that the testback device, pointing to the directory can only be the root directory of the drive letter, the same.

The following method was successfully backed up because it can only be backed up to the root of the drive letter, D-Disk, E-drive

BACKUP DATABASE ultrdstnew to disk= ' D:\backTest20171103.bak '

Disk

British [D?sk] beauty [D?sk]

N.

a disk; a disc; a record.

To create a table syntax:

CREATE TABLE testtable (ID int NOT NULL primary key,name varchar (20))

Insert Data syntax:

Insert into TestTable (id,name) VALUES (1, ' Zhangsan ')

To delete a database:

Drop Database Testdatabase

Restore the Recovery database:

Restore Database Testdatabase from BackTest

Restore

English [r?? St.:( R)] Beauty [r?? St?r,-?stor]

Restore, give back, return, recover, repair

Database full Backup SQL instance:

Use master

GO

DECLARE @num INT--Declares a variable that accepts a return value

EXEC xp_fileexist ' E:\BackTest.bak ', @num output--file exists return 1 does not exist return 0

if (@num = 1)--if present, prompt or do other functions

Begin

DECLARE @filePath varchar, @deviceName varchar (30)

Set @filePath = ' E:\BackTest_ ' +

Replace (replace (varchar, GETDATE (), +), '-', ' _ '), ' ', ' _ '), ': ', ') + '. Bak '

Set @name = ' BackTest ' +convert (VARCHAR), GETDATE (), 9)

Print file already exists, renamed by date

EXEC sp_addumpdevice ' disk ', @name, @file

BACKUP DATABASE Testdatabase

To @name

End

Else--file does not exist

Begin

EXEC sp_addumpdevice ' disk ', ' BackTest ',

' E:\BackTest.bak '

BACKUP DATABASE Testdatabase

To BackTest

End

Exist

British [? G?z?st] beauty [? ɡ?z?st]

Vi.

exist, survive, live, stay, exist

Declare

English [d?? Kle? (r)] beauty [d?? Kler]

Vt.

Declare, announce, declare, say, claim; [Method] For statement

Vi.

To declare, to make a declaration.

Gets the current system time string

SELECT CONVERT (varchar, GETDATE (), 120)

' 2017-11-03 10:06:42 '

SELECT Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', ' _ '), ', ' _ '), ': ', ')

' 2017_11_03_100451 '

Convert

British [k?n?v?:t] beauty [K?N?V?:RT]

(make) a change

Get current system time:

SELECT getdate ()

' 2017-11-03 10:06:42 '

SQL SERVER2014 Database Backup restore