April 16, 2018 at 6:53 am
I have a working script but would like to use a variable in the script to save me having to amend the script every time I run it.
The script is used t o move database files from one location to another as it seems some systems have not followed the model database for file locations (as SSMS would).
/*
/*
This script is to be used for changing the file locations of SQL Databases
Declare the name of the Database you wish to move
*/
DECLARE @DatabaseName nvarchar(1024)
SELECT @DatabaseName = 'AD_Dump'
USE [master]
BACKUP DATABASE [@DatabaseName]
TO DISK = N'I:\SQLBackup\@DatabaseName\@DatabaseName.bak'
WITH COPY_ONLY, NOFORMAT, NOINIT,
NAME = N'@DatabaseName Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
/*
ALTER DATABASE [@DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
*/
EXEC master.dbo.sp_detach_db @dbname = N'@DatabaseName'
RESTORE DATABASE [@DatabaseName] FROM DISK = N'I:\SQLBackup\@DatabaseName\@DatabaseName.bak' WITH FILE = 1, MOVE N'@DatabaseName' TO N'F:\SQLData\@DatabaseName.mdf', MOVE N'@DatabaseName_log' TO N'G:\SQLLog\@DatabaseName.ldf', NOUNLOAD, STATS = 5
/*
ALTER DATABASE [@DatabaseName] SET RECOVERY FULL WITH NO_WAIT
BACKUP DATABASE [@DatabaseName] TO DISK = N'I:\SQLBackup\@DatabaseName\@DatabaseName.bak' WITH RETAINDAYS = 10, NOFORMAT, INIT, NAME = N'@DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
*/
*/
If anybody could help me achieve this I would really appreciate it.
April 16, 2018 at 7:02 am
You can use a variable in the BACKUP statement for the database:DECLARE @DB sysname;
SET @DB = 'master';
BACKUP DATABASE @DB to DISK = 'NUL';
I haven't tested whether you can do the same in the RESTORE statement - I'll leave that to you.
By the way, there's no need to detach the database before you do the restore.
John
April 16, 2018 at 9:01 am
You certainly could use variables, but you'll be changing a variable value at the beginning. Is this harder or different than search/replace?
April 18, 2018 at 3:33 am
Thank you both for your response.
Note ... I have altered the code in my original post as I realised it should have had the Variable instead of the XXX's that I had (as I am running it in that way at present)
John. Your response got me past the first hurdle. Declaring as sysname and removing the square brackets I had in BACKUP DATABASE line seemed to get me past that point.
Steve. Yes it is harder, albeit slightly, but for the amount of databases I have to move it will save a lot of time (and it makes it easier for me to pass instructions on to others in my team to help with the task)
I am now stuck on determining how to get the variable into the line:
TO DISK = N'I:\SQLBackup\@DatabaseName\@DatabaseName.bak'
April 18, 2018 at 5:33 am
terry.home - Wednesday, April 18, 2018 3:33 AMI am now stuck on determining how to get the variable into the line:TO DISK = N'I:\SQLBackup\@DatabaseName\@DatabaseName.bak'
Surely like this?TO DISK = N'I:\SQLBackup\' + @DB + N'\' + @DB + N'.bak'
If that turns out to be illegal, declare another variable @Disk at the beginning and set it to N'I:\SQLBackup\' + @DB + N'\' + @DB + N'.bak'.
John
April 20, 2018 at 8:24 am
Thanks again for your response.
Managed to get it working so thought it might be useful to somebody else one day if I post code back here
/*
This script is to be used for changing the file locations of a SQL Databases to the default location the server is set to
Declare the name of the Database you wish to move
*/
USE [master]
GO
-- Set the Variable name to the database you need to action
DECLARE @DatabaseName sysname
SET @DatabaseName = 'AD_Dump'
-- Start the script
DECLARE @Folders sysname
DECLARE @BackupDirectory NVARCHAR(100)
EXEC master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\MSSQLServer\MSSQLServer', @value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ;
SET @Folders = @BackupDirectory + N'\' + @DatabaseName + N'\' + @DatabaseName + N'.bak';
-- Backup the database
BACKUP DATABASE @DatabaseName
TO DISK = @Folders
WITH COPY_ONLY, NOFORMAT, NOINIT,
NAME = N'AD_Dump Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
/*
ALTER DATABASE @DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
*/
-- Determine default location for Data
declare @rc1 int,
@Datadir nvarchar(4000)
exec @rc1 = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @Datadir output, 'no_output'
if (@Datadir is null)
begin
exec @rc1 = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @Datadir output, 'no_output'
select @Datadir = @Datadir + N'\Data'
end
-- Determine default location for Data
declare @rc2 int,
@Logdir nvarchar(4000);
exec @rc2 = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @Logdir output, 'no_output'
if (@Logdir is null)
begin
exec @rc2 = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @Logdir output, 'no_output'
select @Logdir = @Logdir + N'\Data'
end
DECLARE @ToFolder1 NVARCHAR(100)
SET @ToFolder1 = @Datadir + N'\' + @DatabaseName + N'.mdf'
DECLARE @ToFolder2 NVARCHAR(100)
SET @ToFolder2 = @Logdir + N'\' + @DatabaseName + N'.ldf'
DECLARE @DB_Logname NVARCHAR(100)
SET @DB_Logname = @DatabaseName + N'_log'
-- Detach the database
EXEC master.dbo.sp_detach_db @dbname = @DatabaseName;
-- Restore the Database with files in new location.
RESTORE DATABASE @DatabaseName FROM DISK = @Folders WITH FILE = 1, MOVE @DatabaseName TO @ToFolder1, MOVE @DB_Logname TO @ToFolder2, NOUNLOAD, REPLACE, STATS = 5
GO
/*
ALTER DATABASE [XXX] SET RECOVERY FULL WITH NO_WAIT
GO
BACKUP DATABASE [XXX] TO DISK = N'I:\SQLBackup\XXX\XXX.bak' WITH RETAINDAYS = 10, NOFORMAT, INIT, NAME = N'XXX-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
*/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply