How to backup/restore SQL Server database from command line similar to mysql

Question

I need to automate the migration for some databases from an older server. I know that script a tool using C# for example can be a possibility but I need a simple, fast and effective solution in order to build a batch file to do the job.

Answer 1

Create a .sql file with your backup / restore statement(s) and use SQLCMD

About halfway down that article there is an example of doing a backup.

You can refer to the following pages with information on the T-SQL for doing backups and restores:

• Backup
• Restore

Answer 2

As you have to migrate databases from old server to new, below is the script that will automate it ..

Important: Test it before hand on a TEST Server, so that you understand what the script does ...I bear no Responsibility or Obligation for any amount of DATA LOSS !!!

set nocount on
/****************************************************************************** 
    Author  :: Kin
    Desc    :: Transfer Logins, Databases from one instance/Server to another
*******************************************************************************/
  declare @datafile varchar(255),
            @logfile varchar(255),
            @dbid tinyint,
            @SQLText varchar(8000),
            @dbname varchar(255),
            @destserver varchar(255),
            @SQLText2 varchar(8000)
set @destserver ='' --Destination Server Name goes here.

--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
--print @sqltext
exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
--print @sqltext
exec (@sqltext)

--2. Detach All Local Databases and prepare for Attach on dest.
 --- if you want to filer only some database, then you can do it here !!
if exists(select 1 from tempdb..sysobjects where name like '%#filetable%')
      begin
      drop table #filetable
      end
      create table #filetable (mdf varchar(255),ldf varchar(255),dbid tinyint,dbname varchar(100),fileid tinyint)
      --

      insert #filetable (mdf,dbid,fileid) 
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=1

      insert #filetable (ldf,dbid,fileid) 
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=0

      update u 
      set u.dbname = s.name
      from #filetable u
      inner join master..sysdatabases s 
      on u.dbid = s.dbid


select @dbid = min(dbid) from #filetable where dbid > 4
while @dbid is not null
begin

      select @SQLText = 'alter database '+ dbname from #filetable where dbid = convert(varchar,@dbid) 
      select @SQLText = @SQLText+' set single_user with rollback immediate'
      select @SQLText = @SQLText+' exec master..sp_detach_db ' + dbname from #filetable where dbid = convert(varchar,@dbid)
      print @SQLText
      Exec(@SQLText)

      select @SQLText2 = 'exec opendatasource(''SQLNCLI'',''Datasource='+@destserver+';Persist Security Info=False;Integrated Security=SSPI'').master.dbo.sp_attach_db '''+dbname+'''' from #filetable where dbid = @dbid
      select @SQLText2= @SQLText2+','''+ mdf+'''' from #filetable where dbid = @dbid and mdf is not null
      select @SQLText2=@SQLText2+','''+ ldf+''''  from #filetable where dbid = @dbid and ldf is not null
      print @SQLText2
      Exec(@SQLText)
      select @dbid = min(dbid) from #filetable where dbid > 4 and dbid > @dbid
end

select * from #filetable
drop table #filetable
--Finally Shutdown SQL Server
shutdown with nowait

Answer 3

You need to perform either a physical or logical backup of your database, and then restore it. All this can be done from the command line.

Physical backup

1. SQLCMD

sqlcmd is the default SQL Server command shell. Just run BACKUP DATABASE command in it

sqlcmd -S [server_name] -Q "BACKUP DATABASE [database_name] TO DISK='C:\Backup\backup_file.bak' WITH INIT"

To run a restore use the following command:

sqlcmd -S [server_name] -Q "RESTORE DATABASE [database_name] FROM DISK='C:\Backup\backup_file.bak' WITH RECOVERY

2. Use powershell module for SQL Server

You can install a special module for PowerShell to work with SQL Server which will allow you to run the command: Backup-SqlDatabase

With the following command you can perform a backup of all databases on the server:

Get-ChildItem "SQLSERVER:\SQL\[server-name]\[instance-name]\Databases" | Backup-SqlDatabase -BackupContainer "[backup-path]"

An example of such a command:

Get-ChildItem "SQLSERVER:\SQL\MSI\DEFAULT\Databases" | Backup-SqlDatabase -BackupContainer "c:\Backups2\"

To run a restore use the following command:

Restore-SqlDatabase -ServerInstance '.' -Database 'MyDatabase' -BackupFile 'C:\Backup\MyDatabase.bak' -ReplaceDatabase

Source

Logical backups

In most cases, physical backups are preferable, but if the server on which you restore is an older version than the original one, then the restore operation will fail.

sqlpackage

Logical backups can come to the rescue; for SQL Server you can perform an extract data tier applicator and then export it. This can be done either through SSMS or through the command line utility sqlpackage.exe

Export

sqlpackage.exe /TargetFile:"d:\Backups\export.bacpac" /Action:Export /SourceServerName:"localhost" /SourceDatabaseName:"AdventureWorks" /SourceUser:"sa" /SourcePassword:"my-secret-password"

Import

c:\utils\sqlpackage.exe /SourceFile:"d:\Backups\export.bacpac" /Action:Import /TargetServerName:"." /TargetDatabaseName:"target-database" /TargetUser:"sa" /TargetPassword:"my-password"

mssql-scripter

If you need to get a .SQL database dump, then you can use the [mssql-scripter][https://github.com/microsoft/mssql-scripter] utility. It is very similar to mysqldump for MySQL. To run it you need to install Python, and execute the following:

py -m mssqlscripter -S localhost -d AdventureWorks --schema-and-data -f d:\Backups\AdventureWorks.sql

You can restore such a dump by transferring the dump file to sqlcmd.

sqlcmd -i d:\Backups\AdventureWorks.sql

Logical backups are not Transactionally Consistent; if the data in the database changes during the backup process, this can lead to collisions in the data and problems during the restore process. To avoid this, create a database snapshot and export not the DB itself but the snapshot.

Source