SQL Server - sqlcmd.exe

SQL Server - sqlcmd.exe

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';

Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Maheshkumar S Tiwari edited Revision 1. Comment: Added tags

  • Toni edited Original. Comment: corrected the USE statement and edited some text.

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Toni edited Original. Comment: corrected the USE statement and edited some text.

  • Maheshkumar S Tiwari edited Revision 1. Comment: Added tags

Page 1 of 1 (2 items)