| Author |
Topic |
|
mdhingra01
Posting Yak Master
179 Posts |
 Posted - 2004-06-01 : 08:56:37
|
| Is it possible to set the destination of the backup to a mapped Network Drive? If the drive has a username and password, do I have to/how do I pass them in my BACKUP statement?Thanks |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-01 : 09:18:30
|
| You can backup to a network drive:BACKUP DATABASE database_nameTO DISK = '\\server\share\file.bak'You can't pass a username and password in though. You need to have the NT admin add the account that's running SQL Server Agent service into the permissions for that share/folder.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-01 : 11:35:37
|
| Don't you have space?Better to dump locally and then copy....It'll be faster AND less prone to corruption...Network hiccups and all....MOOBrett8-) |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 14:47:03
|
| Do have space, but its limited. The backup is about 2 GB and the space left on the drive is 4 GB.Is there a way I can truncate the LOG file for this Database and allocate less space for the LDF?So you recomemend backing up locally then transferring? I tried to set that up though a batch file and execute the batch though a package, but it didn't like it either.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 14:50:46
|
| What is your database recovery model set to for this database? If FULL, are you performing transaction log backups?Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-01 : 14:52:54
|
| Add another physical drive...sounds like a smallish box....How big is the log file?What's you're recovery model?How big is the drive in general?How many backups do you have left on disk?Had a dba set up maintenance...never got rid of the old back ups...One day one of my 80GB had no space...I was like, WHAT?db is only 4gb...200 backup files later...boom...Brett8-) |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 15:35:10
|
| Truth of the matter is , before me, there were no backups. My qualifications are no way close to that of dba, I am more a programmer analyst. But since I have been actively involved in trying to get some of our work backed up, I have learned a ton. Some answers for you....Is there a maintanance plan...NOMDF size...91MbLDF size 275Mb (don't know why the Backup for the DB is 2.2GB)Drive is 136 GBBackups left on the disk... No clue, I keep doing a full backup every night of that one Database.Recoevry Model is set to ???? Where do I check that.There is no DBA for this, just me. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 15:38:58
|
| You are probably appending the backups to the same file. Do this:BACKUP DATABASE DBNameTO DISK = 'C:\Temp\DBName.BAK'WITH INITThe important part is WITH INIT, which says overwrite the file if it exists. WITH NOINIT says append.To check your database recovery model, right click on your db in EM, go to properties, then go to options tab.How are you doing the backups?Tara |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 15:44:08
|
| I checked the job and changed the NOINIT to INIT as follows:BACKUP DATABASE [MIS3W] TO DISK = N'E:\BACKUP\Input.MIS3W Backup' WITH INIT , NOUNLOAD , NAME = N'Input.MIS3W backup', NOSKIP , STATS = 10, NOFORMATMy DB recovery Model is Bulk-Logged? What should I set this to? Simple? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 15:45:46
|
| It depends on your business requirements. You probably aren't performing transaction log backups, so you should set it to SIMPLE until you start doing it. Transaction log backups give you the ability to restore to a point in time in case of failure. If you don't need that and can afford to restore to the last backup, then put it at SIMPLE or keep it at Bulk-Logged or FULL and start backing up the transaction log.WITH INIT should fix your space problem.BTW, full backups usually have an extension of BAK. Transaction logs usually have an extension of TRN. Your command doesn't give the file an extension. I would also consider removing that space in the file name.Tara |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-01 : 16:08:51
|
| Thanks Tara! The size is now fixed to 87MB (wooohooo!)I changed the recovery model to: Simple. Should I be adding the file extensions? i.e.BACKUP DATABASE [MIS3W] TO DISK = N'E:\BACKUP\Input_MIS3W.BAK' WITH INIT , NOUNLOAD , NAME = N'Input_MIS3W_backup', NOSKIP , STATS = 10, NOFORMATThanks for all your help on this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-01 : 16:10:51
|
| Yes you should be adding the file extensions like in the code you posted. For backups, I put the database name first then an underscore then the date then .BAK. I don't append backups, I create new ones each day and deletes old ones per the retention schedule. So I've usually got more than one backup on disk at all times. Plus all the t-logs.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-01 : 21:18:13
|
| Now that you've fixed that, run DBCC SHRINKDB('database') and see what size it is. If your backups are only 2.2gb it should shrink it quite a bit. What size is your model db?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 11:13:18
|
| Model is sitting at 896K and the log is 768K.The MIS3W Database is currently sitting at 213Mb and a log of 26Mb. Do I run :DBCC SHRINKDB('MIS3W') in Query Analyzer? |
|
|
|
|
Backing up a database to a logical drive