October 30, 2014 at 1:54 pm
Hello all,
i am new to SQL Server and same goes for t-sql as well.
I am trying to write a simple procedure that does a backup, i understand there is a maintenance plan you can create to do that, but this is for a different purpose...i run the below but it does not seem to work...what am i missing here ?
DECLARE @temp VARCHAR(4000)
SET @temp = 'backup database asdf to disk = 'H:\Backup\SQLTEST02\asdf.bak''
EXEC(@temp)
error i get
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\'.
This is on sql 2008R2...
October 30, 2014 at 1:57 pm
max_scalf (10/30/2014)
Hello all,i am new to SQL Server and same goes for t-sql as well.
I am trying to write a simple procedure that does a backup, i understand there is a maintenance plan you can create to do that, but this is for a different purpose...i run the below but it does not seem to work...what am i missing here ?
DECLARE @temp VARCHAR(4000)
SET @temp = 'backup database asdf to disk = 'H:\Backup\SQLTEST02\asdf.bak''
EXEC(@temp)
error i get
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\'.
This is on sql 2008R2...
You have two extra quotes, try this
DECLARE @temp VARCHAR(4000)
SET @temp = 'backup database asdf to disk = H:\Backup\SQLTEST02\asdf.bak'
EXEC(@temp)
October 31, 2014 at 10:16 am
i tried that as well, but get the below msg...
"incorrect syntax near 'H:'
October 31, 2014 at 10:36 am
With dynamic sql enter two single quotes for each single quote in the string
DECLARE @temp varchar(255) = 'backup database asdf to disk = ''H:\Backup\SQLTEST02\asdf.bak''';
SELECT @temp;
With the BACKUP command a variable can be used instead of dynamic sql.
DECLARE @DS nvarchar(512) = N'H:\Backup\SQLTEST02\asdf.bak';
BACKUP DATABASE asdf
TO DISK = @DS
WITH COPY_ONLY, INIT, SKIP, CHECKSUM;
October 31, 2014 at 11:03 am
Thank you. Just the block by itself works great...how would i go about integrating that into what i am trying to do...
declare @newdb table
(
newdb nvarchar(4000),
loc nvarchar(4000)
);
insert into
select
d.name, 'H:\Backup\SQLTEST02\'
from sys.databases d
left join backupset bs
on bs.database_name = d.name
where bs.database_name is null
and d.name <> 'tempdb'
;
select 'backup database ' + newdb + ' to disk = ''' + loc + newdb + '.bak''' from @newdb --> need to put this scring into below @DS ?
DECLARE @DS nvarchar(512) = N'H:\Backup\SQLTEST02\asdf.bak';
BACKUP DATABASE asdf
TO DISK = @DS
WITH COPY_ONLY, INIT, SKIP, CHECKSUM;
sorry very new to t-sql and trying to learn....
October 31, 2014 at 7:00 pm
hello all,
i was able to get most part working...i took the below article and used it to come up with my strategy of backing up newly created DB that will be scheduled by agent to run every day or so....But when i run the below code i get this error...
fails at this part "backup database @newdb to disk = @loc2;"
Msg 137, Level 16, State 1, Line 21
Must declare the scalar variable "@newdb".
Msg 137, Level 16, State 1, Line 21
Must declare the scalar variable "@LOC2".
http://www.sqlservercentral.com/articles/Administration/trackingdownnewlycreateddatabases/1582/
any help is appreciated....
Use
master
Go
DECLARE
@LOC nvarchar(512) = N'H:\Backup\SQLTEST02\';
DECLARE
@BKP_EXT nvarchar(5);
DECLARE
@retval int;
SET
@BKP_EXT = '.bak';
DECLARE
@newdb table
(
newdb nvarchar(512)
);
DECLARE
@LOC2 table
(
loc2 nvarchar(512)
);
Exec
@retval = sp_track_new_databases
If
(@retval > 0)
insert
into
Select new_db_name
from Last_DB_Track;
select * from @newdb;
insert into @LOC2
select 'backup database ' + new_db_name + ' to disk = ''' + @LOC + new_db_name + '.bak''' from last_db_track;
select * from @LOC2;
backup database @newdb to disk = @loc2;
go
November 3, 2014 at 5:17 am
Umm.... I would not try to do all this yourself.
1. A restore plan is more important than a backup plan. (Backups are no good if they turn out to be corrupted.)
2. To get backups going quickly, use the maintenance plan wizards - these can quickly be configured to backup all the databases.
3. If you have time, customise Ola Hallengren's scripts. These are available at:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply