August 1, 2012 at 11:43 am
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!
August 1, 2012 at 2:01 pm
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.
August 1, 2012 at 3:56 pm
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.
August 1, 2012 at 4:43 pm
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?
[Code="sql"]
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
[/code]
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.
August 1, 2012 at 5:03 pm
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)
August 1, 2012 at 8:59 pm
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.
August 1, 2012 at 10:51 pm
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
August 2, 2012 at 7:28 am
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.
August 2, 2012 at 8:42 am
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!
August 2, 2012 at 9:08 am
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
August 2, 2012 at 4:13 pm
Got everything working and tested, reset schedule for tonight.
My script owner is sa. Tried to change the script from saving to path "C:\Backup\" (this works perfectlly)
to a mapped drive X:\ (tried the full unc path to)
The script fails because the SQL Agent doesn't have permission to write to the network drive.
Found this script to run - But now there is the next problem:
EXEC XP_CMDSHELL 'NET USE X: "\\mynetwork\My_Drives\MyFolder\" '
returned the error:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Where is this setting? SQL Server 2008 - oh thanks Microsoft - now it is the drippy faucet.
Did that - now...
EXEC XP_CMDSHELL 'NET USE X: \\Network\Drives\Folder\Myspace /user:NetDomain\DenSqL ThePasword'
Execute - Output "The command completed Successfully." :w00t:
August 2, 2012 at 8:32 pm
Think twice three times before enabling xp_cmdshell. Enabling it exposes functionality on your SQL Server instance that are better left disabled, which Microsoft agrees with as xp_cmdshell has been disabled by default since SQL 2005.
With SQL Server there are usually multiple ways to accomplish a task and this scenario is no exception. Rather than enabling xp_cmdshell simply insert a new Step 1 to your job of type Operating System (CmdExec) and issue the NET USE there. Then, add a Step 3 the deletes the share to close the loop on the process.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2012 at 9:38 pm
I would use the maintenance plan wizard in SQL Server Management Studio. This is an easy tool to use. And it will automatically grab any new databases. You can schedule it to run any way you need, daily weekly, ...
August 2, 2012 at 10:13 pm
Richardyager (8/2/2012)
I would use the maintenance plan wizard in SQL Server Management Studio. This is an easy tool to use. And it will automatically grab any new databases. You can schedule it to run any way you need, daily weekly, ...
Maintenance Plans are fine for taking backups. For the record Ola's solution satisfies all the points you made as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 3, 2012 at 12:10 pm
While the IT has a backup system in place
I though multiple backup systems would break the backup chain?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply