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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy