Script to archive offline databases

Below is the script 1 that can be used to process the offline databases:

  • Bring database online
  • Backup the database
  • Detach the database
  • Move the MDF and LDF file(s)

The first part to bring the databases online, back them up, and detach them, is:

USE master
GO

DECLARE @backuplocation VARCHAR(500)
SET @backuplocation = 'E:\SQL\Backups\'

CREATE TABLE TempFilesToMove
( filename VARCHAR(500)
)

DECLARE csr CURSOR FAST_FORWARD FOR
    SELECT name FROM sysdatabases
    WHERE (status & 512) = 512

DECLARE @name VARCHAR(255)
DECLARE @sql VARCHAR(1000)

OPEN csr
FETCH NEXT FROM csr INTO @name

IF @backuplocation not like '%\' SET @backuplocation = @backuplocation + '\'

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER DATABASE [' + @name + '] SET ONLINE'
    EXEC (@sql)
    SET @sql = 'BACKUP DATABASE [' + @name + '] TO DISK = N' + CHAR(39) + @backuplocation + @name + '.bak' + CHAR(39)
    EXEC (@sql)
    SET @sql = 'INSERT INTO TempFilesToMove (filename) SELECT filename FROM [' + @name + '].dbo.sysfiles'
    EXEC (@sql)
    EXEC sp_detach_db @name
    FETCH NEXT FROM csr INTO @name
END

CLOSE csr
DEALLOCATE csr

Please ensure you take a copy of the data in TempFilesToMove using the statement below to make sure you have a list of where the files are:

SELECT * FROM TempFilesToMove

The second part to move the MDF and LDF files, is:

DECLARE @backuplocation VARCHAR(500)
SET @backuplocation = 'E:\SQL\Backups\'

DECLARE csr2 CURSOR FAST_FORWARD FOR 
    SELECT filename FROM TempFilesToMove 

DECLARE @filename VARCHAR(500)
DECLARE @cmd VARCHAR(500)
OPEN csr2
FETCH NEXT FROM csr2 INTO @filename

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @cmd = 'MOVE ' + @filename + ' ' + @backuplocation    
    EXEC master..xp_cmdshell @cmd
    FETCH NEXT FROM csr2 INTO @filename
END

CLOSE csr2
DEALLOCATE csr2

DROP TABLE TempFilesToMove

If the service account that runs SQL Server doesn’t have necessary permissions to the archive location, then you may get Access Denied messages when you run the second part. That’s why I suggest taking a copy of the TempFilesToMove table, just in case you need to run it manually.

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.