• Home   /  
  • Archive by category "1"

Sqlcmd Backup Database Unexpected Argumentative Essays

Backing up Microsoft SQL Server is as simple as selecting the MDF and LDF files or using the VSS System Component Writer when the GUI has been launched in expert mode.  For backwards compatibility to our older backup clients, you can also use our legacy SQL selection syntax which results in the backup client's calling and utilizing Microsoft's sqlcmd utility.

 The remainder of this article describes using our SQL plugin, though we strongly encourage you to migrate your selection syntax to use modern backup methods for SQL Server and not follow the methods documented in this article.

The following is an advanced discussion on deciphering how our software actually invokes a Microsoft SQL Server database backup so you know how to restore a database using Microsoft’s own native SQL Server database restoration tools such as Microsoft SQL Server Management Studio Express or even SQLCMD.EXE.

When the GUI has been launched in expert mode you'll see the Backup->SQL Server tab for legacy sqlcmd selections.

If Microsoft's SQL server is installed, the utility 'sqlcmd' should also be installed.  Our backup software relies on this utility to query SQL Server and perform backup tasks.  You can interactively use this utility to query available databases and test account validation independent of our software.

Please open and command prompt and type the following command:


     WIN-O056C6AVHQV me

The output of this command tells us the name of the computer you are logged into followed by the user name you are logged in as. 

The following command tells us the instances of SQL Server running on your computer

C:\Users\Me> sqlcmd -L






So, the above command tells us that on our local machine called 'WIN-O056C6AVHQV' we have an instance of SQL Server called 'SQLEXPRESS' running. (Note: our software only supports backing up database instances running on the computer our software is running on, so we ignore all results from command above which don't have our local computer name 'WIN-O056C6AVHQV' in them).

We now assume your have setup our software to run as the same user you are logged in with, in this example 'me' (i.e., in our software on the "Local Settings" tab the option "Run as specified user:" is selected and the corresponding field specifies 'me').

Issuing the following command will tell us if 'me' has access to the database (i.e. SQL Server is configure for 'mixed mode' authentication and the user named 'me' has been granted database access) as follows:

C:\Users\Me> sqlcmd -S "%COMPUTERNAME%\SQLEXPRESS" -Q "use master"

   Changed database context to 'master'.

If the command returns an authentication error then you'll need to experiment with variations of the following command to determine which SQL Server login name/password will need to be used to access the database (hint: if SQL Server was configured for mixed mode, then try other login accounts, otherwise try login name 'sa' with appropriate database password specified when SQL Server was installed).  Once you have determined the correct username/password to use for SQL Server, please specify them in our software on the "Local Settings" tab by selecting "Backup SQL Server as user:" and entering the appropriate user name and password (please be sure to test the username/password using the   button and "Save" your settings).

C:\Users\Me> sqlcmd -S "%COMPUTERNAME%\SQLEXPRESS" -Q "use master" -U "sa" -P "yourpassword"

   Changed database context to 'master'.

OK, so now that we've determined how to login to SQL Server, we can issue the following command to query all the database names available to backup (use the appropriate version of following based on account validation method you determined above):

C:\Users\Me> sqlcmd -S "%COMPUTERNAME%\SQLEXPRESS" -Q "use master; select

                NAME from sysdatabases"

C:\Users\Me> sqlcmd -S "%COMPUTERNAME%\SQLEXPRESS" -Q "use master; select 

                NAME from sysdatabases" -U "sa" -P "yourpassword"

The result will look something like the following, consisting of the names of all databases available):

   Changed database context to 'master'.








   (5 rows affected)

If we want to backup the 'AdventureWorks' SQL database using our software and legacy SQL syntax, we'd add the following line on the "Backup" "Selections" tab:

+ "$SQL:\SQLSERVER\AdventureWorks"

or the following if you used the default instance name when installing SQL server:

+ "$SQL:\\AdventureWorks"

and to backup all the databases for all SQL instances except any databases named 'tempdb', we'd specify:

+ "$SQL:\<.*>\<.*>" - <tmpdb>

and to backup all non-system databases:

+ "$SQL:\<.*>\<.*>" -<master> -<model> -<msdb> -<tempdb>

and to backup all databases whose name starts with "mo":

+ "$SQL:\<.*>\<^(mo).*>

Our software will issue the appropriate SQL commands to perform a FULL backup of the specified databases at backup time, and then our software will backup the resultant file to our servers.  The local SQL backup will be written in the same folder that the original SQL database file is contained (an easy way to find the disk location of the database is by using the query "use master; select name,filename from sysdatases" in the above sqcmd query).

At backup time, what our software does is to extend the filename by adding "_backup.BAK" to the database name, so for example if the original database was 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks.mdf' then the local database backup will be 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks.mdf_backup.BAK'.

The SQL Server database backups which our software creates are all performed using Microsoft's utilities so that they are standard, portable (if your database is portable), and are easily restored using Microsoft SQL Server's provided administration utilities.  All you need to do is restore the BAK file to your local computer using our software and then use your choice of Microsoft SQL Server administration tools to import/restore the database into SQL Server.  Here is an example of a database dump using sqlcmd:


             "BACKUP DATABASE AdventureWorks TO

             DISK='AdventureWorks.bak' WITH COPY_ONLY,FORMAT"

 Important Note: If the installed version of Microsoft SQL Server supports the "COPY_ONLY" flag, our software will use it to prevent side effects of our database backups impacting your SQL Server backup scheme.  This feature is available in nearly all versions of Microsoft SQL Server EXCEPT those running on Windows XP.  If you are running SQL Server on Windows XP and you have scheduled your own backups using Microsoft SQL Server, we recommend you have our software backup those backup files rather than having our software initiate the SQL backups.

Restoring a BAK file into Microsoft SQL Server is straightforward using Microsoft SQL Server Studio Management Express which is a free utility from Microsoft - however please note that there is a BUG in some versions of this utility which prevents restoring databases backed up using the COPY_ONLY flag (the bug manifests itself as an inability to actually select the database to restore).  If you encounter this you may restore your database from a command prompt using the following command which will overwrite the original database:

C:\Users\Me> sqlcmd -S "%COMPUTERNAME%\SQLSERVER" -Q "RESTORE DATABASE [mydb] FROM DISK='C:\RestorePoint\C\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydb_backup.BAK'"

Here is an example SQL script for restoring to a different database name and underlying file (example for test purposes) which can be run from within  Microsoft SQL Server Studio Management Express or from the sqlcmd utility (please contact your SQL Server database administrator if you are not comfortable with these commands).  First we determine the logical names for the MDF and LDF as follows:


FROM DISK = 'C:\RestorePoint\C\Program         



Next we restore the database to a different database name (i.e. so we don't overwrite the existing database).  In the example below we call the new test database [testyardi]. We then need to tell SQL-SERVER not to overwrite the existing database files so we use the WITH MOVE command to specify new filenames for the data and log files.  The first argument to each MOVE command is the logical name from the query above, and the argument of the TO clause is the new location we choose for the test restore. 

RESTORE DATABASE [testyardi] FROM DISK='C:\RestorePoint\C\Program 


WITH MOVE 'yardi_Data' TO 'C:\Program 


MOVE 'yardi_log' TO 'C:\Program 



Was this article helpful?0 out of 0 found this helpful

Have more questions? Submit a request

Backup and restore SQL Server databases on Linux

You can take backups of databases from SQL Server 2017 on Linux with the same tools as other platforms. On a Linux server, you can use sqlcmd to connect to the SQL Server and take backups. From Windows, you can connect to SQL Server on Linux and take backups with the user interface. The backup functionality is the same across platforms. For example, you can backup databases locally, to remote drives, or to Microsoft Azure Blob storage service.

Backup a database

In the following example sqlcmd connects to the local SQL Server instance and takes a full backup of a user database called .

When you run the command, SQL Server will prompt for a password. After you enter the password, the shell will return the results of the backup progress. For example:

Backup the transaction log

If your database is in the full recovery model, you can also make transaction log backups for more granular restore options. In the following example, sqlcmd connects to the local SQL Server instance and takes a transaction log backup.

Restore a database

In the following example sqlcmd connects to the local instance of SQL Server and restores the demodb database. Note that the option is used to allow for additional restores of log file backups. If you do not plan to restore additional log files, remove the option.


If you accidentally use NORECOVERY but do not have additional log file backups, run the command with no additional parameters. This finishes the restore and leaves your database operational.

Restore the transaction log

The following command restores the previous transaction log backup.

Backup and Restore with SQL Server Management Studio (SSMS)

You can use SSMS from a Windows computer to connect to a Linux database and take a backup through the user-interface.

The following steps walk through taking a backup with SSMS.

  1. Start SSMS and connect to your server in SQL Server 2017 on Linux.

  2. In Object Explorer, right-click on your database, Click Tasks, and then click Back Up....

  3. In the Backup Up Database dialog, verify the parameters and options, and click OK.

SQL Server completes the database backup.

Restore with SQL Server Management Studio (SSMS)

The following steps walk you through restoring a database with SSMS.

  1. In SSMS right-click Databases and click Restore Databases....

  2. Under Source click Device: and then click the ellipses (...).

  3. Locate your database backup file and click OK.

  4. Under Restore plan, verify the backup file and settings. Click OK.

  5. SQL Server restores the database.

See also

One thought on “Sqlcmd Backup Database Unexpected Argumentative Essays

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *