backup in SINGLE_USER MODE

epetroepetro Posts: 69
edited February 23, 2012 4:51PM in SQL Backup Previous Versions
I am trying to reduce the down time for a database move.
I am considering the following steps:
1. Perform scheduled FULL backup.
2. Restore FULL backup with NORECOVERY.
3. Perform DIFF backup in SINGLE_USER mode just prior to tear down.
4. Restore DIFF backup with RECOVERY.

We have applications touching the database and the easiest way to stop them from changing things at 'tear down' time, is to work in SINGLE_USER mode.

The issue I am having is finding a method of calling SQL Backup 6 while in a SINGLE_USER session. From what I have found, the stored procedure uses 2 connections and the command line uses 1. However, when I attempted to utilize the command line i still get error 880.

Here is code: 
"C:\Program Files (x86)\Red Gate\SQL Backup\(LOCAL)\SQLBackupC.exe" -SQL "BACKUP DATABASE [CompressTest] TO DISK = 'L:\CompressRestores20120222\<database>_FULL.sqb' WITH INIT, THREADCOUNT = 7"
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Open the command prompt using a Windows account that is a SQL Server sysadmin.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • epetroepetro Posts: 69
    I adjusted my role to sysadmin, but the error continues. I also tried using the RUN AS to modify the credentials. No dice.
    I quickly toggled the database OFFLINE/ONLINE in case some phantom connection is in the way. Still no go.

    When I look in activity monitor with no filter, there are no processes for my database.

    I would note that there are 2 errors returned inside the command prompt.
    The exact message is this: 
    Backing up CompressTest (full database) to:
  L:\CompressRestore20120222\CompressTest_FULL.sqb

Error 880: BACKUP DATABASE permission denied in database:  (CompressTest)
SQL error 924: Database 'CompressTest' is already open and can only have one user at a time.
SQL Backup exit code: 880
Last SQL error code: 924
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    You mention this as a database move. Is that from one server to another running the same build of SQL or an upgrade to another server running a different version/build of SQL?

    Are you running on a SAN?

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • epetroepetro Posts: 69
    I think I got it.
    I was adjusting the database access from a query window and then changing my connection to Master. 
    USE [Master]
    I thought this freed the single connection, but it does not. I had to 'DISCONNECT' my query session manually.
    Now looking for a way to achieve this result without leaving the query window.
    · Share on Twitter
  • epetroepetro Posts: 69
    Chris,
    both servers are running the same version of SQL 2005 SP4.
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Is this the only database of many on the old server moving to the new one?
    English DBA living in CANADA
    · Share on Twitter
  • epetroepetro Posts: 69
    This database is the only one that will migrate. The others need to remain online.

    Due to the nature of the connection remaining open, I still have the option of running a native DIFF, but I prefer your utility.
    I have a number of these projects to complete and would like to automate as much as possible. Previous solutions required detach/rename/reattach/FULLBackup/restore. This is too time consuming.

    If I can code a solution from one query window, that would be preferred.
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    A solution I saw at PASS I believe in 2009 or 2010 in which a server was being upgraded from SQL2000 to a SQL2008 Cluster used the Full backup being recovered with NORECOVERY followed by Differentials with NORECOVERY followed by Logs with the last one with RECOVERY. It wasn't until the last log with RECOVERY that the database was upgraded. The new server had been created and setup previously so that jobs, users etc matched what was on the old server.

    Now if this was a SAN then I believe that a detach followed by a UNPRESENT (hope this is the right term) from the old server followed by a PRESENT to the new server followed by an attach could work. Again this would need the new server to have the infrastructure to run the app already there.

    We now separate our system databases from our application databases so this could work.

    Just a thought.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • epetroepetro Posts: 69
    My research is really 2 fold.
    We are migrating production databases to a new SAN(and your note of UNPRESENT may yield assistance) and I am trying to convert a series of databases to SQL Storage Compress with minimal downtime. If you are not familiar with SQL Storage Compress, I recommend looking at it for TEST and QA environments with large databases.

    I think I will code for using a native DIFF backup. In production we run nightly FULLs and 15 min logs. If the DIFF is time consuming, I will revisit my options.

    Thanks for the chatter Chris.
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Please explain SQL Storage Compress. I know its not database compression because you are not talking about SQL2008 on onwards.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • epetroepetro Posts: 69
    SQL Storage Compress is a tool available from Redgate which lowers the disk footprint of a database (different from availablity in 2008 Ent). In our case, by an average of 82%.
    I have some databases which require 730 GB in native form, but under this tool (and the preferred extensions mdfx, ndfx) will fit on 155GB.

    The tool requires a background service(HyperBacSrv.exe) be installed between sqlservr.exe and the OS. Read and write operations are intercepted and translated for the compressed file using index files(extensions .index, .index2)

    I don't want to trust it yet in production, but our QA and TEST departments are reporting no issues. The GUI estimates I can save over 3TB on our QA server (which will likely be used by additional restores :) )
    Here is a link to the main page. SQL STORAGE COMPRESS
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Looks interesting. I had seen HyperBac at PASS a few years ago but had just recommended SQLBackup, which we use exclusively for SQL2005, and may still use for SQL2008R2 for split backups as a means to save on backups.

    The technology looked good at that time with a compressed drive containing the SQL database.

    Good luck with your tests and the move.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    From the forum posts you must be the only one using SQL Storage Compress V 6 that has issues.

    Chris
    English DBA living in CANADA
    · Share on Twitter
Sign In or Register to comment.