.::: Backup Database Full & Transaction & Restore Database SQL Server Alternative Full Script :::.

 
still corelation http://teguhth.blogspot.com/2023/05/backup-database-full-transaction.html

1. check database position
select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb')

select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('db_restore');
 

2. backup database & copy to other server
backup database [secretdb] to disk = 'c:\backup\secretdb.bak' with compression
backup log [secretdb] to disk = 'c:\backup\secretdb_log.trn' with compression

3. set offline then online for destination
alter database [db_restore] set offline with rollback immediate
alter database [db_restore] set online

4. make sure database online
SELECT @@servername as ServerName,name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb');
 

5. check file backup composition

restore filelistonly from disk = 'c:\backup\secretdb.bak'
 

6. restore database

restore database db_restore from disk = 'c:\backup\secretdb.bak'
with MOVE 'secretdb' to 'C:\sqldata\secretdb.mdf',
MOVE 'secretdb2' to 'C:\sqldata\secretdb2.mdf',
MOVE 'secretdb_log' to 'C:\sqldata\secretdb_log.ldf',
replace,  recovery

7. check database position
select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('secretdb')

select @@servername as ServerName,db_name(database_id) as DbName, name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('db_restore');