Deprecated: Hook custom_css_loaded is deprecated since version jetpack-13.5! Use WordPress Custom CSS instead. Jetpack no longer supports Custom CSS. Read the documentation to learn how to apply custom styles to your site: in /srv/www/ on line 6078
MSSQL Archives ⋆ SysAdminStuff

How to schedule SQL Server Express backups…

This is a repost of a post from an old blog, made on March 7, 2012, that used to be on:

Original post:

…or how to backup your Microsoft SQL Server databases when you don’t have any access to the db machine and db servers local storage.

I had to made and automate a backup of one of the databases that I use on my job today, but the problem was that the only acces I had to the database was with using a sql server authentication and I didn’t have all the permissions on the base or the server. So here are the steps how to backup your MS SQL database without the use of third party tools or SQL agent (which you can’t use in Express editions of SQL Server).

This will be accomplished using the sqlcmd utility which is a free tools that you get with your Server of Management Studio installation.

  1.  Create a sql script for backup using the BACKUP DATABASE statement, my script looks something like this:
    DECLARE @name NVARCHAR(256);
    set @name='\\server\shared_folder\your_db_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
    Backup Database your_db To Disk = @name;

    this will create backup file in format of your_db_YYYYMMDD.bak
    If you would like a different format of the date check this link for date formats.

  2. Create a bat file that you can put in Task Scheduler, you can put something like this in bat file:
    sqlcmd -S your_server -U username -P password -i C:\some_folder\your_sql_backup_script.sql

    Remember that if you’re using SQL Express you need to use your_server\instance_id to connect to the server.
    You can also use some other switches if you like:

    Microsoft (R) SQL Server Command Line Tool
    Version 10.50.1600.1 NT x64
    Copyright (c) Microsoft Corporation.  All rights reserved.
    usage: Sqlcmd            [-U login id]          [-P password]
      [-S server]            [-H hostname]          [-E trusted connection]
      [-N Encrypt Connection][-C Trust Server Certificate]
      [-d use database name] [-l login timeout]     [-t query timeout]
      [-h headers]           [-s colseparator]      [-w screen width]
      [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
      [-c cmdend]            [-L[c] list servers[clean output]]
      [-q "cmdline query"]   [-Q "cmdline query" and exit]
      [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
      [-u unicode output]    [-r[0|1] msgs to stderr]
      [-i inputfile]         [-o outputfile]        [-z new password]
      [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
      [-k[1|2] remove[replace] control characters]
      [-y variable length type display width]
      [-Y fixed length type display width]
      [-p[1] print statistics[colon format]]
      [-R use client regional setting]
      [-b On error batch abort]
      [-v var = "value"...]  [-A dedicated admin connection]
      [-X[1] disable commands, startup script, enviroment variables [and exit]]
      [-x disable variable substitution]
      [-? show syntax summary]
  3.  Put you bat file in Task Scheduler to run automatically.

Msg 3154, The backup set holds a backup of a database other than the existing database

This is a repost of a post from an old blog, made on March 15, 2012, that used to be on:

Original post:

I got asked for help today at work by a coworker who got the following error when trying to restore a database to a different already existing database:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘database_name’ database. (Microsoft.SqlServer.Smo)

when trying to restore with script he would get something like this:

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘database_name’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The solution for this is simple: you need to add WITH REPLACE option when restoring.

If you’re using script it should look something like this:

RESTORE DATABASE [database_name] FROM  DISK = N'C:\some_directory\backup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

If you’re using GUI you need to mark the on the Options section.