Skip to content

Instantly share code, notes, and snippets.

@otakusid
Created March 26, 2020 18:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save otakusid/777ee95a767a53e59503cbf438adcf9f to your computer and use it in GitHub Desktop.
Save otakusid/777ee95a767a53e59503cbf438adcf9f to your computer and use it in GitHub Desktop.
move files of MS SQL database used for log-shipping to new volume
  • run SQL Management Studio
  • SQL Server Agent - Jobs - LSRestore_MAIN_DB_SERVER_YOUR_DATABASE_NAME - right click - Disable
  • get database files settings
select * from YOUR_DATABASE_NAME.sys.database_files
GO
  • switch database offline
ALTER DATABASE YOUR_DATABASE_NAME SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
  • copy files to new volume
xcopy /F D:\sqldb\YOUR_DATABASE_NAME.mdf E:\
xcopy /F D:\sqllog\YOUR_DATABASE_NAME_log.ldf E:\
  • change database files settings
ALTER DATABASE YOUR_DATABASE_NAME
MODIFY FILE (
    NAME = 'YOUR_DATABASE_NAME',
    FILENAME = 'E:\YOUR_DATABASE_NAME.mdf'
)
GO

ALTER DATABASE YOUR_DATABASE_NAME
MODIFY FILE (
    NAME = 'YOUR_DATABASE_NAME_log',
    FILENAME = 'E:\YOUR_DATABASE_NAME_log.ldf'
)
GO
  • switch database online (SQL Management Studio - Databases - YOUR_DATABASE_NAME - right click - Tasks - Bring online)
  • SQL Server Agent - Jobs - LSRestore_MAIN_DB_SERVER_YOUR_DATABASE_NAME - right click - Enable
  • SQL Server Agent - Jobs - LSRestore_MAIN_DB_SERVER_YOUR_DATABASE_NAME - right click - Start Job At Step...
  • SQL Server Agent - Jobs - LSRestore_MAIN_DB_SERVER_YOUR_DATABASE_NAME - right click - View History - check on errors
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment