Monday, March 12, 2012

Multiple Database or Full Backup

I can't see a way of backing up all databases at once, or
the ability to choose multiple databases to back up. I am
in the process of moving a bunch of databases from one
laptop to another and I would rather not do it one at a
time.
Ideas?
Thanks in advance!!can you take SQL off line, copy database files to new server and attach
databases?
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
> I can't see a way of backing up all databases at once, or
> the ability to choose multiple databases to back up. I am
> in the process of moving a bunch of databases from one
> laptop to another and I would rather not do it one at a
> time.
> Ideas?
> Thanks in advance!!|||I can! In fact I am doing just that as I write this. I
had hoped there would be a way of doing a full backup and
a full restore bring over security intact, etc., but I
hearing there isn't. Seems rather manual this way!
Thanks!!

>--Original Message--
>can you take SQL off line, copy database files to new
server and attach
>databases?
>"Top Cat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
or
am
>
>.
>|||Hi,
Using a automated script you can
1. Backup all the user databases remotely to Laptop
2. After the backup restore it in Laptop ( You might need to start all the
databases in sinle_user using Alter database statement)
I have a script whckh will Backup remotely, please add the restore portion
in the script
Prerequisites
1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am|||I had to restore from a bunch of backup files in a directory a while ago, so
I write a stored proc which loops
the files and do the restore for each file. You can have a similar loop to p
erform the backup as well. But you
really need some TSQL programming skill to write the loop to perform the bac
kups, and you probably want to
verify my code which does the restore:
http://www.karaszi.com/sqlserver/ut...all_in_file.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am

No comments:

Post a Comment