Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script needed for DB Full Backup to Folder on nightly task Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 11:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 123, Visits: 349
I am more on the Access Programming and SQL Query side with basic setup. SQL scripting for task is not my primary business.

While the IT has a backup system in place, they asked me to make a full backup of the production database on a nightly basis and put it into a folder named Archive on the same virtual machine. They will grab it for archives.
This file backup is in addition to the enterprize network going on from the national location. the production is only 90 MB in size, but the data is very important.

I have administrator rights. The Master, Model, and Production DB's need to be backed up - to the same C:\Archive folder with the names plus a date (i.e. Master 09-01-2012)

This seems like a fairly routine application. If there is already a topic on this, please point the way.
Thanks!
Post #1338719
Posted Wednesday, August 1, 2012 2:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:32 AM
Points: 73, Visits: 83
There is a script here http://www.sqlservercentral.com/scripts/Administration/73478/ posted by Pavle-192195 that should do everything you need with the proper parameters. It also does an actual restore to a test DB and then drops it. If you don't want to do an actual restore I would suggest commenting out or removing that part from both the system DB and user DB parts of the script.

Once you're sure the script works how you want just schedule job with at least 2 steps for it to execute twice (once for the system DBs and once for the user DBs) with enough time to finish before the files are moved or copied and you're good to go. Also, if the files are copied, you'll want an additional job step to remove the old files after a certain period of your choice.

I have not tested this script so make sure you do so in a development environment prior to deploying in production.
Post #1338795
Posted Wednesday, August 1, 2012 3:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 123, Visits: 349
Wow, thanks! That was a lot to digest - cut it way down and looked at some other solutions.
Will post some code here that appears to work.

Post #1338875
Posted Wednesday, August 1, 2012 4:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 123, Visits: 349
Wow, thanks! That was a lot to digest - cut it way down and looked at some other solutions.
Will post some code here that appears to work.
This works from the TSQL query window. But, when I created a job and schedule it out 4 minutes ahead, it dosn't run.
Added it to Master in the Job. Any other advice?

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
SET @path = 'C:\Archive\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('RegulatoryDB')
/*
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

Here is the steps for scheduling a job
To create a Transact-SQL job step
1.In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2.Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties.

3.In the Job Properties dialog, click the Steps page, and then click New.

4.In the New Job Step dialog, type a job Step name.

5.In the Type list, click Transact-SQL Script (TSQL).

6.In the Command box, type the Transact-SQL command batches, or click Open to select a Transact-SQL file to use as the command.

7.Click Parse to check your syntax.

8.The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing.

9.Click the Advanced page to set job step options, such as: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and the file or table where SQL Server Agent can write the job step output. Only members of the sysadmin fixed server role can write job step output to an operating system file. All SQL Server Agent users can log output to a table.

10.If you are a member of the sysadmin fixed server role and you want to run this job step as a different SQL login, select the SQL login from the Run as user list.


Post #1338893
Posted Wednesday, August 1, 2012 5:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 123, Visits: 349
Tried ot go bak and edit, and got an error. Noticed that SQL Server Agent was not running. Tried to start and received:
Property PagerSendSubjectOnly is not available for AlertSystem '[denregsql\denregulator]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Post #1338896
Posted Wednesday, August 1, 2012 8:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
Ola hollengren, DB maint script.

Don't reinvent the wheel.

Download.
Run script (it creates sql agent jobs)
Edit job step as needed (number of days to keep, target directory, etc)
Schedule job to run daily.
Miller time.

Post #1338924
Posted Wednesday, August 1, 2012 10:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 7,125, Visits: 12,722
SpringTownDBA (8/1/2012)
Ola hollengren, DB maint script.

Don't reinvent the wheel.

+1

http://ola.hallengren.com/sql-server-backup.html


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1338947
Posted Thursday, August 2, 2012 7:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:32 AM
Points: 73, Visits: 83
Mile Higher, at this point, I think would be best to take SpringTown's advise and download from the site linked by opc.three.

I would advise the same steps in deploying Ola hollengren's script, deploy in dev, make sure you understand the options you want to use and what they do, then when ready, deploy in prod.

Sorry for not suggesting Ola's solution first, given the context of this forum, I should have.

Also if you're going to be doing things similar to this from time to time with SQL Server, I would definitely recommend this book which walks you through many things from start to finish in SQL Server 2008: http://www.amazon.com/Microsoft%C2%AE-Server%C2%AE-2008-Step-Microsoft/dp/0735626049/ref=sr_1_1?s=books&ie=UTF8&qid=1343914160&sr=1-1&keywords=sql+server+2008+step+by+step. It helped me out big time when I was starting out.
Post #1339151
Posted Thursday, August 2, 2012 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 123, Visits: 349
Thanks again, I got sidetracked with the SQL Server Agent failing to start and displaying many errors.
While my DBA contact was out for training, another IT person changed the virtual server password.
Evidently, we needed to change the passwords on each of the services.
We rebooted the SQL Server 2008 R2 to see if the SQL Server Agent service would start and couldn't get back into SQL Server.
So we worked that problem out this morning from the top down.
In retrospect, glad this happened. Lots of good experience gained about SQL 2008 R2.

My previous experience with SQL Server 2000 is being updated. The demand for front-end MS Access and Excel Object Model Data Mining has kept me away from SQL for too long.
All of these responses were very valuable. Including the books. Does SQL Server Central have a Book Review area?
Many Thanks!
Post #1339227
Posted Thursday, August 2, 2012 9:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:32 AM
Points: 73, Visits: 83
I've never looked for one on this site before, however searching book review seems to return a few articles / blogs. Possibly searching by the title of one would be better if you had one in mind: http://www.sqlservercentral.com/search/?q=book+review
Post #1339264
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse