June 28, 2012 at 9:51 am
Write the code into notepad and save the file with a file extension of .sql
the sqlcmd command will run the contents of the .sql file as though you have typed it manually into Studio Management.
June 28, 2012 at 10:04 am
T-SQL is the language the scripts are written in. So when you keyed in the "Select @@version" in Management Studio, you were writing a T-SQL script.
What I think I would suggest, is on another PC (such as your PC) set up a practice database (such as Adventureworks) to practice on.
Google is your best friend for the commands, but I would also include in your search "microsoft sql" somewhere, so you're not trying to run a backup using MySQL or Oracle commands...
MSDN article on the T-SQL Backup command: http://msdn.microsoft.com/en-us/library/ms186865.aspx
Start simple, get a feel for what you're doing, then (carefully!) try it against the "live" database. Paranoiac me would say, when you think you've got a script that is going to work the way you want, first run a backup of the DB using Management Studio, *THEN* try your script. If your script doesn't work as expected, you've got that first backup to fall back on.
June 28, 2012 at 10:07 am
Thanks. I am posting to fast and not giving myself a chance. I figured out how to get this to work. So now I do have a basic batch file that will launch the .sql file and actually do the backup.
I suppose I could leave it as is and write seperate files for each db. There are only 7 databases. I do want to pursue other way's to switch to one script that will back them all up to individual files however.
OK I think I have a grasp on getting my backups running. I changed the script with some code I found with a google search. It will now backup all my databases and name them with a date\time stamp. I'm am testing this in a test environment so now I can modify the data and try a restore of my backups and see how all that goes.
Thanks guy's for getting me started here!
June 28, 2012 at 11:14 am
OK here is the script I'm using and it's working however. If I run the script again it seems to just be appending the next backup to the same file. I think we would rather have seperate files or even have it over write the current file when it backs up. I looked up the command for overwriteing the file and tested if I add a WITH INIT in the command it will overwrite. Would this be the best way to go about running these backups.
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:\Backup\'
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
WITH INIT
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
June 28, 2012 at 11:28 am
If you're planning to trigger this from within a DOS batch file, you could set up the batch file to either outright delete the previous backup files, or move them to a new folder. Renaming would be possible, but a bit more problematic because of using the date in the filenames.
Maybe something like this:
@echo off
c:
cd\{backup directory}
md oldbackups
move *.* c:\{Backup directory}\oldbackups
Insert command line code to fire off your backup script here
This would keep old backups, as the T-SQL backup script would create a new backup file with the current date every time. Eventually you'd need to go in and clean out the "oldbackups" folder, or use the DOS "forfiles" to clean it out, so you don't fill up the drive with backups. Also, replace the c: with the appropriate drive letter for where your backups are going...
June 28, 2012 at 12:57 pm
Thanks this is a possability also. I suppose I could write another batch file to go in and clean out the old backups and schedule that to kick off once a month or so.
June 28, 2012 at 1:05 pm
Actually, my initial post didn't make sense so here's an updated one.
First off I'd try using a date format that includes a timestamp. So instead of CONVERT(VARCHAR(20),GETDATE(),112) , I'd try CONVERT(VARCHAR(20),GETDATE(),121) or something like that. That should force a new file to be created.
http://networking.ringofsaturn.com/SQL/howtoformatdatetime.php
If not, then maybe play around with NOSKIP?
June 29, 2012 at 9:33 pm
Is not clear, if you can use to create a SQL Server 2000 maintenance plans in SQL Server 2008 Management Studio, but my guess is no.
Try harder and harder to know Platform thick heels shoes everywhere!
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply