Is it possible to enter a command line command (like in a batch file) to attach a detached database to SQL Server, in stead of opening the management studio and doing it in there?
-
can you explain with an exampleAnjali– Anjali2014-09-17 16:59:50 +00:00Commented Sep 17, 2014 at 16:59
Add a comment
|
3 Answers
you need to use: sqlcmd Utility
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.
Then use CREATE DATABASE (Transact-SQL) to do the attach and sp_detach_db (Transact-SQL) to do the detach. The sp_attach_db (Transact-SQL) is going to be removed in a future version of Microsoft SQL Server.
3 Comments
Michel
Very nice. Worked like a charm. Did have to enable 'named pipes' to run SqlCmd, don't know why, but other than that: chapeau!
Michel
I have a batch file with this content:
sqlcmd -Usa -PPASSWORD -i"N:\SqlServer\attachexample_database.sql" and the content of the attachexample_database.sql is CREATE DATABASE example_database ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\example_database.mdf') FOR ATTACH ; GO GOMichel
and for detach I have this batch file
sqlcmd -Usa -PPASSWORD -i"N:\SqlServer\detachexample_database.sql" and the content of the detachexample_database.sql is sp_detach_db example_database;` GOIf you need to specify the log file name: USE master; GO; CREATE DATABASE DBNAME ON ( FILENAME = 'C:\DBFILE.mdf') LOG ON ( FILENAME = 'C:\DBLOGFILE_log.ldf') FOR ATTACH; GO; And to detach: USE master; GO; EXEC sp_detach_db 'DBNAME', 'true'; GO;
Comments
Small gotcha, it won't tell you what is wrong when using sqlcmd and your mdf/ldf files are marked read-only. Make sure they are read-write.
