SQL Server - sqlcmd.exe

  • Article

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job.
This utility uses OLE DB to execute Transact-SQL batches. sqlcmd is part of SQL Server 2005 and SQL Server 2008.

Example:

In command line call

sqlcmd -S <servername> -E -i sqlrestore_mydb.sql

where sqlrestore_mydb.sql is in the actual directory and like:

USE MASTER;

-- Restore even if used:

ALTER DATABASE [mydb] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [mydb] SET  SINGLE_USER;

-- Restore from backup file with fulltext index

RESTORE DATABASE [mydb]
FROM DISK = 'D:\SQL\MSSQL\BACKUP\mydb.bak' WITH REPLACE,
MOVE 'mydb_Data'  TO 'D:\SQL\MSSQL\Data\mydb.mdf',
MOVE 'mydb_Log'   TO 'D:\SQL\MSSQL\Data\mydb_log.ldf',
MOVE 'sysft_mydb' TO 'E:\Index\ftmydb'

-- Create a user for login

USE [mydb];

CREATE USER [mydbsa] FOR LOGIN [mydbsa];
EXEC sp_addrolemember N'db_owner', N'mydbsa';