Tech: Backup MSSQL from command line

August 18, 2009 on 3:14 pm | In Tech | No Comments

Found a lot of help here and there. But ran into a stupid problem that nobody discussed.

Step 1: Create the Backup script.

Go into SQL Server Management Console
Right click on your database -> Tasks -> Backup
Do up the settings as needed
Look at the Script button on the top, select Script Action To File
Name it something like SQLBackup.sql

Now I have a script file that I can execute from the command line using sqlcmd.exe

Step 2: Use sqlcmd

Create a batch file to run the SQL script with sqlcmd like so:

sqlcmd -S . \SQLEXPRESS -i “C:\Documents and Settings\Administrator\Desktop\Backup Scripts\SQLBackup.sql” > “C:\Documents and Settings\Administrator\Desktop\Backup Scripts\DBBackup.log”

This will even dump the log of what command did into a log file.

QED.

Supposedly.

But I kept getting an error:

Msg 911, Level 16, State 11, Server PPSSERVER\SQLEXPRESS, Line 1
Could not locate entry in sysdatabases for database ‘SomeStuff’. No entry found with
that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Server PPSSERVER\SQLEXPRESS, Line 1
BACKUP DATABASE is terminating abnormally.

Made no sense. The ‘SomeStuff’ database is obviously there, but sqlcmd won’t run right.

Tested the SQLBackup.sql script in the server management console and it works fine there. But just not with sqlcmd.

Finally, I created another script to output the list of databases on the server, just create a script file SQL.sql with:

exec sys.sp_databases
go

And execute similiarly with sqlcmd.

And ‘SomeStuff’ is NOT in the list of databases it can find…

Hmm…

Finally, it turns out there are 2 instances of SQL on the machine, on /Server, and on /Server/SQLEXPRESS

‘SomeStuff’ is on /Server.

Dunno how that happened.

So, amended the sqlcmd command to:

sqlcmd -S . -i “C:\Documents and Settings\Administrator\Desktop\Backup Scripts\SQLBackup.sql” > “C:\Documents and Settings\Administrator\Desktop\Backup Scripts\DBBackup.log”

And happiness.