Command line tool to backup , restore, detach, attach database

1. Is there a command line tool using which I can backup and restore a SQL Server database ?

2. Is there a command line tool using which I can detach and attach a SQL Server database ?

I need to write a DOS batch file that can use these command line tools to give a simplied way to a novice user to perform these operations, without going through Enterprise Manager.

Pls. give some examples for syntax if possible
LVL 1
rajesh_khaterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gad_flyCommented:
Hi

for backup you can use somthing like this :

// For creating logical backup
USE master  
EXEC sp_addumpdevice 'disk',' BackupName',
   DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ BackupName.dat'

-- Back up the full Test database.
BACKUP DATABASE Test TO  BackupName

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gad_flyCommented:

and for Log files you can use this :

// Create log file back
USE master
EXEC sp_addumpdevice 'disk', 'LogName',
   'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\LogName.dat'


-- Back up the log of the MyNwind database.
BACKUP LOG Test
   TO LogName

gad_flyCommented:

for Restore :

RESTORE DATABASE Test
   FROM BackupName



IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

gad_flyCommented:

and For Deattach and Attach You can use this

EXEC sp_detach_db @dbname = 'DBName'
EXEC sp_attach_single_file_db @dbname = 'DBName',
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\DBName.mdf'

All address i used before can change and All the DB Name and phisically File name it can be change

I hope it's Usefull

Mohammad Pourebtehaj
bholzCommented:
And to use all these SQL files you can use osql.exe, the command line version of the Query Analyzer.

Bernd
Aneesh RetnakaranDatabase AdministratorCommented:
rajesh_khater,
> 1. Is there a command line tool using which I can backup and restore
> a SQL Server database ?


OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE urDatabase TO DISK = 'c:\urdb.dat_bak'"

similarly u can try the restore process


> 2. Is there a command line tool using which I can detach and attach a
> SQL Server database ?
OSQL -Usa -Pmypassword -n -Q "master..sp_detach_db 'urDB' "
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.