January 10, 2011 at 2:00 pm
Hello,
I am newbie to SQL but would need your guidance on what I am trying to get below.
1) Backup to a drive
2) restore the backup taken on target node.
I am using below command to backup
declare @vDate, @vTime, @vBackuplocation
set @vBackuplocation='c:\temp';
backup @database_name to disk = @vBackuplocation;
Restoring
I am trying to use wildcards to restore the database but not sure why i am gettign error while i try to restore,
declare @db_FileName nvarchar(30), @db_location nvarchar(30), @db_mdf_location nvarchar(30),
@db_logfile nvarchar(30), @db_ldf_location nvarchar(30);
set @db_name = test;
set @db_FileName = te;
set @db_logFile= te_log;
.
.
.
restore database @db_name
from disk= @db_location
with move @db_FileName to @db_mdf_location + '.mdf',
move @db_logfile to @db_ldf_locaiton +'.ldf'
Please advice...
Thanks.
January 10, 2011 at 6:49 pm
What parts are working?
In the restoring section, the variables being populated are not in quotes. (Probably a typo in the post here, since you would also get a compile error.)
You might need to build the command as a string that is executed with sp_executesql
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 11, 2011 at 7:12 am
Hi,
Thank you very much for quick reply. when I try to put the quotes then I get the error as
Incorrect syntax near +
any ideas of what I am doing wrong?
January 11, 2011 at 9:01 am
itsjustme (1/11/2011)
Hi,Thank you very much for quick reply. when I try to put the quotes then I get the error as
Incorrect syntax near +
any ideas of what I am doing wrong?
Where are you trying to put the quotes?
When you get the "Incorrect syntax" error, double-click on it. It will take you to the line of code with the error - which line is that?
The "Incorrect syntax near + " makes it sound like you're trying to put quotes in the restore command - what exactly is the code that you're trying to run (with the quotes).
The quotes should be on the SET statements:
set @db_name = 'test';
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 11, 2011 at 9:06 am
Hi Wayne,
here is the code that I am trying to write..
declare @db_name nvarchar(255), @db_location_name nvarchar(255), @db_log_name nvarchar(255)
, @db_name_time nvarchar(255);
set @db_name = 'test';
set @db_log_name ='test_log';
set @db_location_name='c:\temp\test.bak';
set @db_name_time = 'test01' ;
Restore database @db_name_time
from disk =@db_location_name
With move @db_name to @db_location_name + '.mdf',
move @db_log_name to @db_location_name + '.ldf';
the error is pointing out to +
January 11, 2011 at 9:31 am
itsjustme (1/11/2011)
Hi Wayne,here is the code that I am trying to write..
declare @db_name nvarchar(255), @db_location_name nvarchar(255), @db_log_name nvarchar(255)
, @db_name_time nvarchar(255);
set @db_name = 'test';
set @db_log_name ='test_log';
set @db_location_name='c:\temp\test.bak';
set @db_name_time = 'test01' ;
Restore database @db_name_time
from disk =@db_location_name
With move @db_name to @db_location_name + '.mdf',
move @db_log_name to @db_location_name + '.ldf';
the error is pointing out to +
Try this:
declare @db_name nvarchar(255),
@db_location_name nvarchar(255),
@db_log_name nvarchar(255),
@db_name_time nvarchar(255),
@db_mdfname nvarchar(255),
@db_ldfname nvarchar(255);
set @db_name = 'test';
set @db_log_name ='test_log';
set @db_location_name='c:\temp\test.bak';
set @db_name_time = 'test01' ;
SET @db_mdfname = @db_location_name + '.mdf';
SET @db_ldfname = @db_location_name + '.ldf';
Restore database @db_name_time
from disk =@db_location_name
With move @db_name to @db_mdfname,
move @db_log_name to @db_ldfname;
I haven't tried the restore command with variables before, so I have no clue as to whether or not this will work. But, since the error is complaining about the "+", let's try it first by removing those from the restore command.
In BOL for RESTORE, I don't see where it allows variables for the MOVE parameters. So, if the above doesn't work, you might have to try this:
declare @sql nvarchar(max);
set @sql = 'Restore database ' + @db_name_time +
' from disk = ' + QuoteName(@db_location_name, char(39)) +
' With move ' + QuoteName(@db_name, char(39)) + ' to ' + QuoteName(@db_mdfname, char(39)) +
', move ' + QuoteName(@db_log_name, char(39)) + ' to ' + QuoteName(@db_ldfname, char(39)) + ';';
execute sp_executesql @sql;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 12, 2011 at 7:10 am
Thank you very much! that really worked... just adding the text in single quotes and defining '.mdf' and '.ldf' to variables really worked...
January 12, 2011 at 8:55 am
itsjustme (1/12/2011)
Thank you very much! that really worked... just adding the text in single quotes and defining '.mdf' and '.ldf' to variables really worked...
Just so I can learn from this also, did you have to make the restore command a dynamic sql string, or did the move parameter allow the variables by themselves?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 12, 2011 at 9:06 am
either way works, I tried with move option and created a sql agent job to automate the backup\restore jobs.
January 12, 2011 at 9:12 am
itsjustme (1/12/2011)
either way works, I tried with move option and created a sql agent job to automate the backup\restore jobs.
Cool, thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply