DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to restore a database with SQLCMD?

Execute the following Microsoft SQL Server T-SQL batch script in Management Studio Query Editor to demonstrate the use of SQLCMD (new SQL Server 2005 replacement for OSQL) command line utility for database restore.

The existing AdventureWorks database is restored from a backup file on the SQL Server 2008 [DELLSTAR\SQL2008] named instance :

-- Basic RESTORE DB syntax with overwrite current

RESTORE DATABASE [Alpha] FROM  DISK = N'F:\backup\AW8.BAK' WITH  FILE = 8,

NOUNLOAD, REPLACE,  STATS = 10

GO

-------------

-- Variable file source database RESTORE - dynamic backup / restore filename

DECLARE @FilePath nvarchar(256) =  N'F:\data\backup\AW8.BAK'

RESTORE DATABASE [AW8] FROM  DISK = @FilePath WITH  FILE = 1,

NOUNLOAD, REPLACE,  STATS = 10

GO

/* Processed 23096 pages for database 'AW8', file 'AdventureWorks2008_Data' on file 1.

Processed 2 pages for database 'AW8', file 'AdventureWorks2008_Log' on file 1.

Processed 36 pages for database 'AW8', file 'FileStreamDocuments' on file 1.

RESTORE DATABASE successfully processed 23133 pages in 5.764 seconds (31.353 MB/sec).

*/

------------

 

-- RESTORE DATABASE to a new location syntax

RESTORE DATABASE [NEWAdventureWorks] FROM  DISK = N'F:\temp\AW.bak'

WITH  -- FILE = 6, 

MOVE N'AdventureWorks_Data' TO N'F:\data\AW.mdf', 

MOVE N'AdventureWorks_Log' TO N'F:\data\log\AW8.ldf', 

NOUNLOAD,  STATS = 10

GO

------------

WARNING: xp_cmdshell & SQL Server security

-- SQL restore existing database - SQL Server SQLCMD - SQL xp_cmdshell

USE MASTER;

DECLARE  @RestoreStatement VARCHAR(256)

DECLARE  @BackupFile VARCHAR(64)

DECLARE  @CommandText VARCHAR(512)

DECLARE  @SQLCMD VARCHAR(64)

 

SET @BackupFile = '''f:\sample\backup\AW.bak'''

 

SET @SQLCMD = 'SQLCMD -SDELLSTAR\SQL2008 -E -Q '

 

SET @RestoreStatement = 'USE MASTER; ALTER DATABASE AdventureWorks

SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE

AdventureWorks FROM DISK='

 

SET @CommandText = @SQLCMD + '"' + @RestoreStatement + @BackupFile + '"'

 

PRINT @CommandText

 

/*

SQLCMD -SDELLSTAR\SQL2008 -E -Q "USE MASTER; ALTER DATABASE AdventureWorks 

SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

RESTORE DATABASE AdventureWorks FROM DISK='f:\sample\backup\AW.bak'"

*/

-- Execute command prompt

EXEC MASTER.dbo.Xp_cmdshell   @CommandText

GO

/*  

Output

Changed database context to 'master'.

Processed 21288 pages for database 'AdventureWorks', file 'AdventureWorks_Data' 

on file 1.

Processed 3 pages for database 'AdventureWorks', file 'AdventureWorks_Log' 

on file 1.

RESTORE DATABASE successfully processed 21291 pages in 4.769 seconds 

(34.877 MB/sec).

NULL

*/

------------

Related articles:

How to find out the last restore date for a database?

Tutorial: sqlcmd Utility

Using SQL Server 2005 sqlcmd Utility DatabaseJournal.com

RESTORE (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE