Run in SSMS or Query Analyzer
Copy Only Backup for SQL 2005 and SQL 2008
Is there any way that my ad hoc backups will not interrupt the sequence of my backup plan?
2009-06-30
3,948 reads
Run in SSMS or Query Analyzer
Set nocount on declare @tblpaths table ( [property]varchar (100), [path]varchar (200) ) declare @returncodeint, @instancenamevarchar (100), @pathnvarchar(4000) /* get instance name */Select @instancename = @@servername insert into @tblpaths ([property], [path]) select 'Instance Name', @instancename /* find default data path */ exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\mssqlserver',N'defaultdata', @path output, 'no_output' if @path is null begin exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\setup',N'sqldataroot', @path output, 'no_output' set @path = @path + '\data' insert into @tblpaths ([property], [path]) select 'Database Data Directory', @path end else begin insert into @tblpaths ([property], [path]) select 'Database Data Directory', @path end /* find default log path */ exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\mssqlserver',N'defaultlog', @path output, 'no_output' if @path is null begin exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\setup',N'sqldataroot', @path output, 'no_output' set @path = @path + '\data' insert into @tblpaths ([property], [path]) select 'Database Log Directory', @path end else begin insert into @tblpaths ([property], [path]) select 'Database Log Directory', @path end /* find binn directory */exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\setup',N'sqlbinroot', @path output, 'no_output' insert into @tblpaths ([property], [path]) select 'Binn Directory', @path /* find backup directory */exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\mssqlserver',N'backupdirectory', @path output, 'no_output' insert into @tblpaths ([property], [path]) select 'Backup Directory', @path select * from @tblpaths