Some of the things that you ask about will definitely affect your ability to run your backups in the middle of the night - and some of them won’t.
Become a Patron of Ask Leo! and go ad-free!
Backing up at night
If you want to run your backups at 1:00 am, then you need to leave your machine on and make sure that standby or hibernate don’t turn on automatically. That’s all part of your power settings. As long as you're plugged into power, make sure that those are set to not happen - if they are configured to happen at all.
Programs only run when the machine is on and running backup software is just another program. That means that it needs to be turned on, it can’t be hibernating, and it shouldn’t be in standby.
Passwords
The password that you’re being asked for is more than likely just your screensaver. It simply prevents someone from accessing your computer if you step away. It does not prevent your backup from running.
I have an article, “Why is my screensaver asking for a password and how do I fix that?” That will walk you through the steps that you need to remove the password prompt when you come back from your screen saver.
DVDs
Yes, if you backup to DVDs, then someone needs to swap out the DVDs when they fill up. Your computer can’t magically reach out and swap in blank ones. Because of the amount of data that we typically backup these days, DVDs just aren’t really big enough. That’s why I much prefer an external drive. You can almost certainly backup your entire computer in one operation without having to interact with it. And depending on the size of the drive, you may be able to keep several backups on that single drive.
If you must backup to DVDs, the more practical solution is not to try and backup in the middle of the night. Do it at a time when you’re available to swap out the DVDs.
Most backup programs, when setting up automated schedules, usually ask for your Windows password so that they can run even if you aren’t logged in.