Using legacy sqlcmd syntax to backup Microsoft SQL Server

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


Powered by Zendesk