search

dbcli / mssql-cli

A command-line client for SQL Server with auto-completion and syntax highlighting
BSD 3-Clause "New" or "Revised" License
1.35k stars 192 forks source link

Can this be used to script a db restore #529

Open dkozlowski569 opened 2 years ago

dkozlowski569 commented 2 years ago

Can MSQL-CLI be used to automate the restore of a DB ?

chrisg123 commented 1 year ago

I would assume so. I haven't scripted it but I do it manually with the following:

ALTER DATABASE "THE_DB" SET OFFLINE WITH ROLLBACK IMMEDIATE

RESTORE DATABASE "THE_DB" FROM DISK = 'some\path\to\my.bak' WITH REPLACE,STATS=1

ALTER DATABASE "TH_DB" SET ONLINE

You can run this query to see the restore progress

SELECT
   session_id as SPID, command, a.text AS Query, start_time, percent_complete,
   dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

If you take a look at mssql-cli -h the -i or --input_file parameters should let you pass in a script.

mauro-miatello commented 1 year ago

RESTORE FROM DISK runs into server, so if the mssql-cli connection is from remote I think this doesn't work