[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3439
  • Last Modified:

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
0
rajesh_khater
Asked:
rajesh_khater
  • 4
5 Solutions
 
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

0
 
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

0
 
gad_flyCommented:

for Restore :

RESTORE DATABASE Test
   FROM BackupName



0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
0
 
bholzCommented:
And to use all these SQL files you can use osql.exe, the command line version of the Query Analyzer.

Bernd
0
 
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' "
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now