SQL Backup Script Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Backup Script Issue

Author

Topic

bkstevens1
Starting Member

6 Posts

Posted - 2015-02-17 : 16:35:30

Hello,
I am trying to piece together a script to 1) create a folder on a local drive with the date (this part works). 2). Do a complete backup of all data bases in our SQL. (This part used to work). 3). Have the created backup files saved into the folder that was created in step 1 of this request.

Through research and code others have posted I have two pieces that work separately. But the code stops when I put them together.

Here is what I have so far.
------------------------------------------------------------------

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- Original code created by unknown

-- Create a folder to save the file
-- code snipit by tkizer

DECLARE @cmd nvarchar(500), @folderName varchar(100)

SET @folderName = 'Backup-' + REPLACE(CONVERT(varchar(10), GETDATE(), 101), '/', '')
SET @Path = 'E:\SQLBackup\'
SET @cmd = ' mkdir ' + @path + @folderName

EXEC master..xp_cmdshell @cmd

-- please change the set @path = 'change to your backup location'. for example,
-- SET @path = 'C:\backup\'
-- or SET @path = 'O:\sqlbackup\' if you using remote drives
-- note that remotedrive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive
-- you have to chnage you sql server accont to a network account and add that user to have full access to the network drive you are backing up to

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @folderName + @name + '_' + @fileDate + '.BAK'
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
-----------------------------------------------------------------
As you can hopefully see, I'm trying to setup a code to run nightly for backups. Although the original code works it just dumped the database backups into a folder and we have a lot of databases so it was not very easy to go through and delete them when the disk started getting full, and I wanted to separate out the backups into their own daily folders.
I would greatly appreciate any help in helping me figure out why the code stops after creating the folder.

Thanks
bkstevens1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-02-17 : 18:57:33

You seem to be generating the fully qualified file name but not performing an actual backup.

SET @fileName = @path + @folderName + '\' + @name + '_' + @fileDate + '.BAK' -- Is the separator needed?

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

bkstevens1
Starting Member

6 Posts

Posted - 2015-02-18 : 11:30:23

quote:
Originally posted by Bustaz Kool

You seem to be generating the fully qualified file name but not performing an actual backup.
SET @fileName = @path + @folderName + '\' + @name + '_' + @fileDate + '.BAK' -- Is the separator needed?

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

Bustaz Kool,

First let me say, thank you for the reply and second that I know only enough to be dangerous when it comes to this type of coding.

So to be honest, I really don't know. That line was as it was in the original script, except for the addition of the @folderName. I was trying to get the script to install the BAK files into that newly created folder, so, if there is anything wrong in that line it was what I added. The original script worked just fine. It just didn't put the files into a new uniquely named daily folder. Which is what my goal is to accomplish.

------------------------------------------------------------------
Here is the original script that I started with. I do not know who created it or I would give credit, but this script does work, it just doesn't do everything I'm looking to automate.

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- please change the set @path = 'change to your backup location'. for example,
-- SET @path = 'C:\backup\'
-- or SET @path = 'O:\sqlbackup\' if you using remote drives
-- note that remotedrive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive
-- you have to change you sql server account to a network account and add that user to have full access to the network drive you are backing up to

SET @path = 'C:\tmp\sqlserver\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
------------------------------------------------------------------

Thanks
bkstevens1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-02-18 : 15:33:58

You dropped the line that performed the actual backup:

WHILE @@FETCH_STATUS = 0 
BEGIN 
   SET @fileName = @path + @folderName + '\' + @name + '_' + @fileDate + '.BAK' -- Is the separator needed?
   BACKUP DATABASE @name TO DISK = @fileName
END

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

bkstevens1
Starting Member

6 Posts

Posted - 2015-02-18 : 15:43:52

quote:
Originally posted by Bustaz Kool

You dropped the line that performed the actual backup:
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @folderName + '\' + @name + '_' + @fileDate + '.BAK' -- Is the separator needed?
BACKUP DATABASE @name TO DISK = @fileName
END

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

Busta Kool,

I must have deleted that line by accident. I didn't think I made any changes that low in the code so I didn't check it.

It seems to be working now, thank you so much for your help! Now I just need to get it setup and running in the task manager and test it over a day or two to make sure the results are what I need.

Have a great day!
And thanks again!
bkstevens1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-02-18 : 19:25:16

I live to serve...

Glad it worked out for you.

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources